Conditional Formatting based on text?
 

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.

Conditional Formatting based on text?
 

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

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


"KarenH" wrote:


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.

Conditional Formatting based on text?
 

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

On Feb 21, 4:10 pm, KarenH wrote:
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.

Conditional Formatting based on text?
 

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.
--

--Andy Smith



"KarenH" wrote:


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.

Conditional Formatting based on text?
 

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

"Andy Smith" wrote in message
...
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.
--

--Andy Smith



"KarenH" wrote:


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.