Conditional Formats to Find Words in Text String

  • Thread starter Thread starter Daren
  • Start date Start date
D

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.
 
=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)))
 
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.
 
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))))
 
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.
 
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
 
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
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Records using Criteria that Contain Text 2
Need help. Conditional formatting problems 1
Conditional Formatting 1
Comparison of columns using Conditional Formatting 3
Conditional Formatting 3
Excel Excel Conditional Format 2
Conditional formatting by key words 4
Conditional Formatting to Show Red Highlight in Text Field 8

Back
Top