formula to find cell values containing X

M

Melissa

I would like to use conditional formatting to highlight cells containing a
particular word. Is there a formula I can use in the Conditional Formatting
dialog box?
 
J

Jacob Skaria

Hi Melissa

1. Select the cell/Range
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

(For whole content of the cell)
=H1="x"

('x' within the cell content )
=ISERROR(SEARCH("x",I1))=FALSE

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
 
T

T. Valko

('x' within the cell content )
=ISERROR(SEARCH("x",I1))=FALSE
Click to expand...

You can reduce that to:

=SEARCH("x",I1)

It'll evaluate to either the error #VALUE! or a number from 1 to LEN(I1).

When it evals as an error the format won't be applied. When it evals as *any
number other than 0* the format will be applied.

In logical tests Excel evaluates 0 as FALSE and *any number other than 0* as
TRUE.
 
Top