Excel: Apply Conditional Formatting if Cell Contains Partial Text

By /

Often you may want to apply conditional formatting to cells that contain specific partial text in Excel.

Fortunately this is easy to do by using the New Rule option within the Conditional Formatting feature in Excel:

The following example shows exactly how to do so.

Example: Apply Conditional Formatting if Cell Contains Partial Text in Excel

Suppose we have the following dataset that shows the names of various basketball teams:

Suppose we would like to highlight each team that contains “avs” somewhere in the name.

To do so, highlight the values in the range A2:A13, then click the Conditional Formatting icon on the Home tab, then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type in the following formula into the box:

=SEARCH("avs", A2)

Then click the Format button and choose a color to use:

Each team that contains “avs” in the name will automatically be highlighted:

Note that the SEARCH function is case-insensitive.

If you would like to perform a case-sensitive search instead, you should use FIND(“avs”, A2) instead since the FIND function is case-sensitive.

Related:

x

Related terms:

  1. What is Excel: Apply Conditional Formatting if Cell Contains Text
  2. Excel: Apply Conditional Formatting if Cell Contains Formula
  3. How to do conditional formatting in google sheets if another cell contains text?
  4. How to apply conditional formatting to a cell based on the value in another cell?
  5. How to Apply Conditional Formatting Based on Adjacent Cell in Excel?
  6. How do I check if cell contains a partial text in Excel?
  7. Excel: Apply Conditional Formatting to Second Highest Value
  8. Excel: Apply Conditional Formatting to Yes/No Values
  9. Excel: Apply Conditional Formatting to Dates Older Than 1 Year
  10. How to Apply Conditional Formatting to Dates within 6 Months in Excel