Conditional Format (text strings)

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

--
Regards
Frank Kabel
Frankfurt, Germany


JoeBlow wrote:
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,

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!

In article ,
JoeBlow wrote:

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.

=SEARCH("LP",A1)

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

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 wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

JoeBlow wrote:
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
for example select the range
A1:H200
after this goto the conditional format dialog and enter the formula
(the cell index will adapt automatically)

--
Regards
Frank Kabel
Frankfurt, Germany


JoeBlow wrote:
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 wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

JoeBlow wrote:
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.

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!

In article ,
JoeBlow wrote:

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.

Joe

Select the whole table first, then FormatCF and formula is:

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

Gord Dibben Excel MVP

On Sat, 05 Jun 2004 20:48:47 GMT, JoeBlow wrote:

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 wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

JoeBlow wrote:
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,

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.


Frank Kabel wrote:

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)

--
Regards
Frank Kabel
Frankfurt, Germany

JoeBlow wrote:
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 wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

JoeBlow wrote:
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.

What is the range you want to apply conditional formatting?

"JoeBlow" wrote in message
...
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.
[...]