Explanation
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10 cells in B2:B11, and B2 will change to the address of the cell being evaluated each time, since B2 is relative.
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:
=ISNUMBER(FIND("dog",A1))
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 .
Explanation
Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range “things”:
--ISNUMBER(SEARCH(things,B4)
The SEARCH function returns the position of the value if found, and the #VALUE error if not found. For B4, the results come back in an array like this:
{8;#VALUE!;#VALUE!}
The ISNUMBER function changes all results to TRUE or FALSE:
{TRUE;FALSE;FALSE}
The double negative in front of ISNUMBER forces TRUE/FALSE to 1/0:
{1;0;0}
The SUMPRODUCT function then adds up the results, which is then tested against zero:
=SUMPRODUCT({1;0;0})>0
Any non-zero result means at least one value was found, so the formula returns TRUE, triggering the rule.
Ignore empty things
To ignore empty cells in the named range “things”, you can try a modified formula like this:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things<>"",things),B4)))>0
This works as long as the text values you are testing don’t contain the string “FALSE”. If they do, you can extend the IF function to include a value if false known not to occur in the text (i.e. “zzzz”, “####”, etc.)
Case-sensitive option
SEARCH is not case-sensitive. To check case as well, replace SEARCH with FIND like so:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Preventing false matches
One problem with this approach is you may see false matches caused by substrings that appear inside longer words. For example, if you try to match “dr” you may also find “Andrea”, “drink”, “dry”, etc. since “dr” appears inside these words. This happens because SEARCH automatically performs a “contains” match.
For a partial fix, you can add space around the search words (i.e. " dr “, or “dr “) to avoid catching “dr” in another word. But this will fail if “dr” appears first or last in a cell, or appears next to punctuation. This can be partially addressed by adding space also around the original text. To add space to the start and end of both at the same time, you can try a formula like this:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
However, this won’t fix problems caused by punctuation.
If you need a more complete solution, one option is to normalize the text first in a helper column , taking care to also add a leading and trailing space. Then you can search for whole words surrounded by spaces.
