If you want to highlight cells that contain certain text, you can use a simple formula that returns TRUE when a cell contains the text (substring) that you specify. For example, if you want to highlight any cells in the range B2:B11 that contain the text “dog”, you can use: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B2 in this case. The formula itself uses the SEARCH function to find the position of “dog” in the text. If “dog” exists, SEARCH will return a number that represents the position. If “dog” doesn’t exist, SEARCH will return a #VALUE error. By wrapping ISNUMBER around SEARCH, we trap the error, so that the formula will only return TRUE when SEARCH returns a number. We don’t care about the actual position, we only care if there is a position.
Case sensitive option
SEARCH is not case-sensitive. If you need to check case as well, just replace SEARCH with FIND like so:
Looking for more than one thing?
If you want to highlight cells that contain one of many different strings, you can use the formula described here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
