Conditional Formats to Find Words in Text String

[Daren]

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.

 

[Gary Brown]

=ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER(FIND("SHOPPE",UPPER($A1)))

 

[Daren]

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.

 

[Sheeloo]

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 wit
=NOT(ISNUMBER(FIND("PHARMACY",UPPER($A1)))+ISNUMBER(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER(FIND("SHOPPE",UPPER($A1))))

 

[Roger H.]

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

 

[Ashish Mathur]

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

 

[Ashish Mathur]

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jarek Kujawa]

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

 

[Bernard Liengme]

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

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

 

[Bernard Liengme]

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