Conditional Format (text strings)

[JoeBlow]

I have a large table that only has text in the cells. I need to search
each cell for for certain text and highlight every cell contains a
particular word in the text string. The word can be anywhere in the
string. For example, below are some typical text strings that may be
found in the cells:

Cell 1 may contain: "LP: paint wall. RM: fix table"
Cell 2 may contain: "JR: wash windows. LP: paint door"

I would like highlight every cell that contains "LP" in the string (no
matter where it occurs in the string). I can't figure out how to
program Conditional Format.

Thanks for any help.

 

[Frank Kabel]

Hi
use the following formula within the conditional format dialog (e.g.
for cell A1):
=ISNUMBER(FIND("LP",A1))

 

[Domenic]

Hi,

Assuming that your data starts at Cell A1, try the following:

1) Select your data
2) Format > Conditional > Formula Is
3) Enter the formula =ISNUMBER(FIND("LP",A1))
4) Choose your formatting
5) Click OK

Hope this helps!

 

[Aladin Akyurek]

=SEARCH("LP",A1)

 

[JoeBlow]

Thanks Frank. That works for cell A1, but what do you put in the dialog
box if you want to conditionally format the entire table. I'm talking 7
coloums x 250 rows. I put your formula in, but it doesn't format all
the cells.

Please help. Thanks.

 

[Frank Kabel]

Hi
for example select the range
A1:H200
after this goto the conditional format dialog and enter the formula
(the cell index will adapt automatically)

 

[Domenic]

You need to make sure that you select your entire table before you
Format > Conditonal Format > Etc...

Also, you'll note from Aladin's post that it's not necessary to include
ISNUMBER in the formula.

So then you can have either,

=FIND("LP",A1) which is case sensitive

OR

=SEARCH("LP",A1) which is not case sensitive

Hope this helps!

 

[Gord Dibben]

Joe

Select the whole table first, then Format>CF and formula is:

Enter Frank's formula, making sure the A1 is left relative(no $ signs)

Gord Dibben Excel MVP

 

[JoeBlow]

Frank,

I've been trying that. For some reason, it will shade a few of the cells
that contain "LP", but it won't shade them properly--and I made sure they
were selected. Another wierd thing is if I change the "LP" in
[=ISNUMBER(FIND("LP",A1))] to "RM", it will not properly shade cells
containing "RM" either. I can only think of these reasons.

1. I am selecting a range of cells before I go to "Conditional Format".
But, the "Formula Is" dialag contains only A1 in
[=ISNUMBER(FIND("LP",A1))]. The dialog box does not give me an option to
select/type a range of cells in place of A1.
2. I intially created the spreasheet in a version later than EXCEL 97. I
was able to open the file in 97 and I'm currenlty editing it in 97. I'm
not sure if something happend to my data.

Each time I try again, I SELECT ALL, go to conditional format dialog box,
and then clear all of the condidtions to make sure that I'm not over
lapping old conditions. I'm at a loss here.

Thanks for the help. This is an urgent matter for me.

 

[Aladin Akyurek]

What is the range you want to apply conditional formatting?

Frank,

I've been trying that. For some reason, it will shade a few of the cells
that contain "LP", but it won't shade them properly--and I made sure they
were selected. Another wierd thing is if I change the "LP" in
[=ISNUMBER(FIND("LP",A1))] to "RM", it will not properly shade cells
containing "RM" either. I can only think of these reasons.

1. I am selecting a range of cells before I go to "Conditional Format".
But, the "Formula Is" dialag contains only A1 in
[=ISNUMBER(FIND("LP",A1))]. The dialog box does not give me an option to
select/type a range of cells in place of A1.
2. I intially created the spreasheet in a version later than EXCEL 97. I
was able to open the file in 97 and I'm currenlty editing it in 97. I'm
not sure if something happend to my data.

Each time I try again, I SELECT ALL, go to conditional format dialog box,
and then clear all of the condidtions to make sure that I'm not over
lapping old conditions. I'm at a loss here.

Thanks for the help. This is an urgent matter for me.

[...]

 

[JoeBlow]

Aladin,

My table range is A1:G234. Column A and Row 1 are the heading of my table (so
the data is from B2:G234). I see no reason you can't select the entire table.
Even if I select a portion of the table, it won't work correctly.

What is the range you want to apply conditional formatting?

Frank,

I've been trying that. For some reason, it will shade a few of the cells
that contain "LP", but it won't shade them properly--and I made sure they
were selected. Another wierd thing is if I change the "LP" in
[=ISNUMBER(FIND("LP",A1))] to "RM", it will not properly shade cells
containing "RM" either. I can only think of these reasons.

1. I am selecting a range of cells before I go to "Conditional Format".
But, the "Formula Is" dialag contains only A1 in
[=ISNUMBER(FIND("LP",A1))]. The dialog box does not give me an option to
select/type a range of cells in place of A1.
2. I intially created the spreasheet in a version later than EXCEL 97. I
was able to open the file in 97 and I'm currenlty editing it in 97. I'm
not sure if something happend to my data.

Each time I try again, I SELECT ALL, go to conditional format dialog box,
and then clear all of the condidtions to make sure that I'm not over
lapping old conditions. I'm at a loss here.

Thanks for the help. This is an urgent matter for me.

[...]

 

[JoeBlow]

Thanks for everyone who responded. I got it to work!

Aladin,

My table range is A1:G234. Column A and Row 1 are the heading of my table (so
the data is from B2:G234). I see no reason you can't select the entire table.
Even if I select a portion of the table, it won't work correctly.

What is the range you want to apply conditional formatting?

Frank,

I've been trying that. For some reason, it will shade a few of the cells
that contain "LP", but it won't shade them properly--and I made sure they
were selected. Another wierd thing is if I change the "LP" in
[=ISNUMBER(FIND("LP",A1))] to "RM", it will not properly shade cells
containing "RM" either. I can only think of these reasons.

1. I am selecting a range of cells before I go to "Conditional Format".
But, the "Formula Is" dialag contains only A1 in
[=ISNUMBER(FIND("LP",A1))]. The dialog box does not give me an option to
select/type a range of cells in place of A1.
2. I intially created the spreasheet in a version later than EXCEL 97. I
was able to open the file in 97 and I'm currenlty editing it in 97. I'm
not sure if something happend to my data.

Each time I try again, I SELECT ALL, go to conditional format dialog box,
and then clear all of the condidtions to make sure that I'm not over
lapping old conditions. I'm at a loss here.

Thanks for the help. This is an urgent matter for me.

[...]

 

[Robbins2U]

As I read JoeBlows problem I have a question
What if he doesnt want to have to change the "LP", "RM" etc all the time
How can he do a search/find other than the find button
Is there a dialog box that can pop up that you can fill in to do a search?

 

[Peo Sjoblom]

You can replace the hard coded values by a cell reference

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom