Conditional Formatting based on text?

[Guest]

Is it possible to implement conditional formatting based on text contained
in a cell? Example, user wants to be able to highlight all the cells in a
spreadsheet for a given entity (which will change all the time) so I'm
looking for something like "cell contains xyz" -- but as far as I can tell,
it works only with numeric values.

Am I trying to do something that is impossible?

Thanks in advance.

 

[Guest]

Until somebody suggests something a little more polished, use a formula like:

=LEN(E4)=LEN(SUBSTITUTE(E4,"Smith",""))

 

[oatmeal]

I think you can do conditional formatting, select the "between"
option, and put a in the first cell and z in the second. I just tested
it out and it seems to work even if the cell has multiple letters.
Once I type a number in it the format goes away.

Is that what you were looking for?

-Steven

 

[Guest]

Select the whole range, or even the whole row(s) or column(s) where you want
this conditional formatting, then do Format/Conditional Formatting.

In the upper let corner of the dialog box, change "Cell Value Is" to
"Formula Is". Now it expects a formula which evaluates to TRUE or FALSE
(although non-zero and zero will do). Any cell references in the formula are
subject to absolute and relative addressing, so if you chose all of Column A,
the formula should be:

=FIND("xyz",A1) > 0

Then, if you select cell A2 and do Format/Conditional Formatting, you'll find:

=FIND("xyz",A2) > 0

and so on for A3, A4, etc.

 

[Joerg]

I think Andy's solution is the way to go. Only 2 additions:

1) since the OP mentioned that the text changes all the time, it would be
better not to hard code it into the condition. Assuming that D1 contains the
text the formula would be =FIND($D$1,A1) > 0 or just =FIND($D$1,A1)

2) If the texts should match exactly the formula =$D$1=A1 could be used
(first '=' identifies the following stuff as a formula and D1=A1 is a valid
formula, resulting either in TRUE or FALSE)

Joerg