Explanation
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”. The formula looks like this:
=SEARCH($G$2,B4)=1
G2 contains the text to search for, and B4 is the cell being tested. The formula is evaluated relative to the active cell at the time the rule is created for each cell in the range, which is B4. Each cell in B4:G12 is evaluated separately. Since B4 is entered as a relative reference , it will change to the cell being evaluated. Since cell G2 is entered as an absolute reference ($G$2), it will not change.
The formula uses the SEARCH function to match cells that begin with “mi”. SEARCH returns a number that indicates a position when the text is found, and a #VALUE! error if the text is not found. When SEARCH returns the number 1, we know that the cell value begins with “mi” because the location of the text is 1. The formula returns TRUE when the position is 1 and FALSE for any other value. For example, in cell B4, the formula evaluates like this:
When this formula returns TRUE, it triggers a conditional formatting rule that applies a bright fill color to cells that begin with “mi”, as seen in the worksheet above. The value in cell G2 can be changed at any time and the conditional formatting will instantly update.
With a named input cell
You can simplify the formula a bit by using a named range for cell G2. For example, if we name G2 “input” the formula can be revised as follows:
=SEARCH(input,B4)=1
The named range automatically behaves like an absolute reference so there is no need to lock any references.
Case-sensitive option
=FIND(input,B4)=1
Like the SEARCH function, FIND returns the position of the text as a number. However, FIND is automatically case-sensitive.
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 .
