Conditional Formats to Find Words in Text String
 

Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

=ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FI ND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A 1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER( FIND("SHOPPE",UPPER($A1)))
--
------------
Hope This helps,
Sincerely,
Gary Brown



"Daren" wrote:

Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

Thanks, Gary. I was able to paste the formula into conditional formatting
but the formula did not hightlight the cells green when I changed the
pattern. Do you know what the issue might be? Thanks again.

"Gary Brown" wrote:

=ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FI ND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A 1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER( FIND("SHOPPE",UPPER($A1)))
--
------------
Hope This helps,
Sincerely,
Gary Brown



"Daren" wrote:

Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

What do meand by 'when I changed the pattern.'?

If you have this formula and choose RED highlighting then it will highlight
only if none of the patterns are found...

To change to GREEN either FORMAT the cell as GREEN and then apply the
formula in Conditional Formatting...
or have another conditions with
=NOT(ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBE R(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPE R($A1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUM BER(FIND("SHOPPE",UPPER($A1))))
"Daren" wrote:

Thanks, Gary. I was able to paste the formula into conditional formatting
but the formula did not hightlight the cells green when I changed the
pattern. Do you know what the issue might be? Thanks again.

"Gary Brown" wrote:

=ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FI ND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A 1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER( FIND("SHOPPE",UPPER($A1)))
--
------------
Hope This helps,
Sincerely,
Gary Brown



"Daren" wrote:

Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

As an alternative solution using a somewhat simpler formula, you could type
your criteria words into a range of cells and then use an IF function. Given
that you typed them in P1:P6, use the formula =COUNTIF($P$1:$P$6,A1)<1.This
will give you the option of more easily changing your criteria, if needed,
directly from the worksheet itself.

"Sheeloo" wrote:

What do meand by 'when I changed the pattern.'?

If you have this formula and choose RED highlighting then it will highlight
only if none of the patterns are found...

To change to GREEN either FORMAT the cell as GREEN and then apply the
formula in Conditional Formatting...
or have another conditions with
=NOT(ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBE R(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPE R($A1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUM BER(FIND("SHOPPE",UPPER($A1))))
"Daren" wrote:

Thanks, Gary. I was able to paste the formula into conditional formatting
but the formula did not hightlight the cells green when I changed the
pattern. Do you know what the issue might be? Thanks again.

"Gary Brown" wrote:

=ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FI ND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A 1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER( FIND("SHOPPE",UPPER($A1)))
--
------------
Hope This helps,
Sincerely,
Gary Brown



"Daren" wrote:

Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

Hi,

Type the 5 stings (pharmacy, drug etc.) in I7:I11. Now while on cell E5 go
to Format Conditional formatting, select Formula is and input the
following formula

=ISERROR(MATCH($E5,$I$7:$I$11,0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Daren" wrote in message
...
Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when
the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria.
Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

Hi,

Forgot to mention - please select the format of your choice.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Daren" wrote in message
...
Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when
the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria.
Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

Excel 2003
one way:
FormulaIs
=AND(A1<"pharmacy",A1<"drug",A1<"rx",A1<"scrip t",A1<"shoppe")
and select red fill pattern
then copy pastespecial as formats


On 26 Lut, 17:13, Daren wrote:
Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. *I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. *Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

On an otherwise unused sheet, enter a column your special words.
Select this range and give it a name (I used 'mydata') with
Insert/Name/Define

On the worksheet to be formatted, select the range use Format Conditional
Formatting and specify Formulas IS =ISNA(VLOOKUP(A1,mydata,1,FALSE)) then
pick either a font colour or a pattern fill.

You must use the naming method since CF does not like references to other
worksheets - names are OK
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jarek Kujawa" wrote in message
...
Excel 2003
one way:
FormulaIs
=AND(A1<"pharmacy",A1<"drug",A1<"rx",A1<"scrip t",A1<"shoppe")
and select red fill pattern
then copy pastespecial as formats


On 26 Lut, 17:13, Daren wrote:
Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria.
Can
you assist with the formula?

Thanks.

Conditional Formats to Find Words in Text String
 

On an otherwise unused sheet, enter a column your special words.
Select this range and give it a name (I used 'mydata') with
Insert/Name/Define

On the worksheet to be formatted, select the range use Format Conditional
Formatting and specify Formulas IS =ISNA(VLOOKUP(A1,mydata,1,FALSE)) then
pick either a font colour or a pattern fill.

You must use the naming method since CF does not like references to other
worksheets - names are OK
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Daren" wrote in message
...
Hello,
I have many records that include pharmacy, drug, rx, script, or shoppe in
them. I need a conditional format to highlight these cells in red when
the
cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria.
Can
you assist with the formula?

Thanks.