We have an Excel spreadsheet and we'd like to somehow highlight cells containing specific text. For example, we have the following data in cells A1 through A10 and we want to highlight any row containing the letter 'F', in this case cells A3, A5 and A8.
ABC
BCD
DEF
GHI
EFG
JKL
CDE
FGH
KLM
HIJ
First we need a formula that allows us to identify whether a cell contains the target text. We can use the Search() function, like this: =SEARCH("F",A1). Search returns the position of the first letter in the search string if it is found, otherwise it returns a #Value error. This works well, but we are not interested in the position of the text, just whether or not it is found. We also need to deal with the #Value error when the text is not found. Here's how:
=NOT(ISERROR(SEARCH("F",A1)))
This formula will return TRUE if the text is found, or FALSE if it is not. Now all we need is a way to highlight the cells containing the search text. Enter conditional formatting!
Move the cursor to cell A1 and hit Formatting --> Contitional formatting in the menu. Change the dropdown from "Cell value is" to "Formula is". Then paste your formula in the formula box and set the background to red. This will have the effect of setting the cell background to red if the cell contains the text we are looking for. Hit OK to save.
Now hit the paintbrush button in the toolbar to copy this format to all the other cells. You should see the cells with the letter F highlighted in red.
