Microsoft Excel Functions & Formulas

Use the SUBSTITUTE function to search for text

This tip can help you look for specific text in a list and mark each

occurrence. The search text is specified in cell B13. Specify the

SUBSTITUTE function as a condition to search for, and shade each

cell where the text string is found.

4

To search for text:

1. Copy the data shown in Figure 10-18 to a new worksheet,

and select cells B2:B11.

2. On the Format menu, click Conditional Formatting.

3. Select Formula Is and type the following formula:

=LEN(B2)<>LEN(SUBSTITUTE(B2,B13,"")).

4. Click Format.

5. From the Patterns tab, select a color and click OK.

6. Click OK.

Conditional Formatting with Formulas 263

10

Figure ...