Excel: Apply Conditional Formatting if Cell Contains Partial Text

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.

Thus, if we instead used =SEARCH(“AVS”, A2) then each team with “avs” in the name would still be highlighted.

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: The Difference Between SEARCH vs. FIND Functions in Excel

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Apply Conditional Formatting to Yes/No Values
Excel: Apply Conditional Formatting Based on Adjacent Cell
Excel: Apply Conditional Formatting if Cell Contains Formula

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *