Conditional Format (text strings)

  • Thread starter Thread starter JoeBlow
  • Start date Start date
J

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.
 
Hi
use the following formula within the conditional format dialog (e.g.
for cell A1):
=ISNUMBER(FIND("LP",A1))
 
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!
 
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.
 
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)
 
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!
 
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
 
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.
 
What is the range you want to apply conditional formatting?

JoeBlow said:
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.
Click to expand...
[...]
 
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.


Aladin said:
What is the range you want to apply conditional formatting?

JoeBlow said:
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.
Click to expand...
[...]
Click to expand...
 
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.

Aladin said:
What is the range you want to apply conditional formatting?

JoeBlow said:
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.
Click to expand...
[...]
Click to expand...
Click to expand...
 
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?
 
You can replace the hard coded values by a cell reference

--


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

Regards,

Peo Sjoblom
 
Back
Top