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:
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.
The following tutorials explain how to perform other common operations in Excel: