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.
Related formulas
In this example, the goal is to apply conditional formatting to cells that begin with specific text, which is entered in cell G2. The highlighting is done automatically with a conditional formatting rule applied to the range B4:G12. The rule type is "Use a formula to determine which cells to format...Highlight cells that begin with
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 cell in B4:G12, and the reference to B4 will change to the address of each cell being evaluated...Highlight cells that end with
The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem. The formula in C5, copied down, is: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))...Cell contains one of many things
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...Highlight cells that contain one of many
Related functions
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of find_text inside within_textas a number. SEARCH supports wildcards, and is not case-sensitive....SEARCH Function
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.FIND Function
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBERto check that a cell contains a numeric value, or that the result of another function is a number.ISNUMBER Function
Related videos
In this video, we'll look at how to use a formula to apply conditional formatting. Formulas allow you to make powerful and flexible conditional formatting rules that highlight just the data you want. Let's take a look. Excel provides a large number of conditional formatting presets, but there are...How to apply conditional formatting with a formula
In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once. This is a great alternative to filtering, because you can see the information you're looking for highlighted in context. Let...How to build a search box with conditional formatting
Using conditional formatting, It's easy to highlight cells that match a certain condition. However, it's a little trickier to highlight entire rows in a list that contains multiple columns. In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in...How to highlight rows with conditional formatting
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula. Let's take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average...Conditional formatting based on a different cell
