How to Apply Conditional Formatting Formula in Excel If Cell Contains Text (6 Methods)

Conditional formatting in Excel allows you to highlight cells based on specific criteria. In this tutorial, we’ll focus on using formulas to highlight cells containing text values. Let’s explore six methods:


Highlight Cells If Cells Contain Text
Highlight Cells If Cells Contain Specific Text
Highlight Cells If Cells Contain Case-Sensitive Text
Highlight Cells If Cells Contain Partial Matched Text
Highlight Cells If Cells Contain One of Many Text String (OR Logic)
Highlight Cells If Cells Contain Several Text Strings (AND Logic)


Method 1 – Highlight Cells Containing Any Text:

Sample dataset to Apply Excel Conditional Formatting Formula to Highlight Cells If Cells Contain Text

  • Select the cells where you want to apply conditional formatting.

Select the cells to apply conditional formatting

  • Go to the Styles group and click Conditional Formatting.
  • Choose New Rule. 

Click on the New Rule command from the Conditional Formatting

  • Select Use a formula to determine which cells to format.
  • Enter the formula:
=ISTEXT(B6)
    • Where B6 is the first cell in your range.

Apply formula with the ISTEXT function in Conditional Formatting

  • Customize the formatting (e.g., fill color) in the Format Cells dialog.
  • Confirm to apply the conditional formatting.

Select the Fill color to format cells with conditional formatting

  • A Format Preview will appear.
  • Click OK to apply the Conditional Formatting.

Finalize the New Formatting Rule

  • We will get the highlighted cells with conditional formatting containing the text values only.

Highlighted cells with conditional formatting for containing the text values

Read More: Conditional Formatting Multiple Text Values in Excel


Method 2 – Highlight Cells with Specific Text:

  • Similar to Method 1, use the New Rule dialog.
  • Enter the formula:
=EXACT($G$6,C6)
    • Where G6 contains your specific lookup value.
  • Apply formatting as desired.

Apply formula with the EXACT function in Conditional Formatting

  • This method helps find exact matches (e.g., Sauce – Soya) in your dataset.

Conditional Formatting to highlight cells if they contain specific text

Read More: Applying Conditional Formatting for Multiple Conditions in Excel


Method 3 – Case-Sensitive Text Highlighting:

  • Again, use the New Rule dialog.
  • Enter the Formula:
=FIND($G$6,C6)
    • Where G6 is your lookup value.
  • Click on Format to apply the formatting as before.
  • After selecting the desired format, click OK to apply.

Apply formula with the FIND function in Conditional Formatting

  • This method differentiates case-sensitive matches.

Conditional Formatting to highlight cells if they contain case-sensitive text


Method 4 – Partial Matched Text Highlighting:

  • We’ll use the SEARCH function to highlight cells containing partially matched text.
  • The SEARCH function is case-insensitive and finds matches partially or fully.
  • Example: Suppose we want to highlight cells containing wine.
    • Enter the formula in the Format values where this formula is true box:
=SEARCH($G$6,C6)
  • Apply the desired formatting and click OK.

Apply formula with the SEARCH function in Conditional Formatting

  • The cells will be highlighted based on partial matches (e.g., sauce, corn).

Conditional Formatting to highlight cells if they contain partially matched text

  • The SEARCH function ignores case sensitivity.
  • You can see in the below GIF that the cells are highlighted according to the value in cell G6 (e.g., sauce, corn). It is irrespective of the case, as the SEARCH function is not case-sensitive.

GIF to visualize the changes in cell formatting with values


Method 5 – OR Logic for Multiple Text Strings:

  • We’ll highlight cells matching any of the values in cells G6 and G7 (e.g., Sauce or Wine).
  • Use a formula combining IF, OR, ISNUMBER, and SEARCH functions:
  • Enter the following formula in the Format values where this formula is true box:
=IF(OR(ISNUMBER(SEARCH($G$6,C6)),ISNUMBER(SEARCH($G$7,C6))),1,0)
  • Apply formatting and click OK.

Combining multiple functions in Conditional Formatting for OR logical operation

  • The cells will be highlighted if they match either Sauce or Wine.

Conditional Formatting to highlight cells if they contain one of many text strings

  • You can see in the below GIF that the cells are highlighted according to the values in cells G6, change the values in G6 and G7, and get all the highlighted values for any of the matches.

GIF to visualize the changes in cell formatting with values

Read More: Excel Conditional Formatting Formula with IF


Method 6 – AND Logic for Multiple Text Strings:

  • Similar to Method 5, but we’ll highlight cells matching both criteria in cells G6 and G7 (e.g., Sauce and Soya).
  • Use a formula combining IF, AND, ISNUMBER, and SEARCH functions:
  • Enter the following formula in the Format values where this formula is true box:
=IF(AND(ISNUMBER(SEARCH($G$6,C6)),ISNUMBER(SEARCH($G$7,C6))),1,0)
  • Apply formatting and click OK.

Combining multiple functions in Conditional Formatting for AND logical operation

  • The cells will be highlighted if they contain both Sauce and Soya.

Conditional Formatting to highlight cells if they contain several text strings

  • You can see in the below GIF that the cells are highlighted according to the values in cells G6 and G7. Change the values in G6 and G7 and get all the highlighted values for both matches.

GIF to visualize the changes in cell formatting with values


Download Practice Workbook

You can download the practice workbook from here:


Related Articles

<< Go Back to Conditional Formatting Formula | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo