Conditional Formats to Find Words in Text String

's Computer Specifications

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.

's Computer Specifications

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

's Computer Specifications

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(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+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.

's Computer Specifications

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))))
"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(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+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.

's Computer Specifications

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)))+ISNUMBER(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+ISNUMBER(FIND("SCRIPT",UPPER($A1)))+ISNUMBER(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(FIND("DRUG",UPPER($A1)))+ISNUMBER(FIND("RX",UPPER($A1)))+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.

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:7EE067DF-D67E-43F2-8256-(E-Mail Removed)...
> 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.

's Computer Specifications

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Daren" <(E-Mail Removed)> wrote in message
news:7EE067DF-D67E-43F2-8256-(E-Mail Removed)...
> 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.

's Computer Specifications

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 26 Lut, 17:13, Daren <Da...@discussions.microsoft.com> 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 whenthe
> cells do NOT satisfy the pharmacy, drug, rx, script, or shoppe criteria. *Can
> you assist with the formula?
>
> Thanks.

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:de44801a-0985-4a5a-82d6-(E-Mail Removed)...
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 26 Lut, 17:13, Daren <Da...@discussions.microsoft.com> 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.

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:7EE067DF-D67E-43F2-8256-(E-Mail Removed)...
> 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.

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find cells with conditional formats	mjones	Microsoft Excel Discussion	2	29th Aug 2010 01:18 PM
find a string of nth occurance & pick next 3 words	Eddy Stan	Microsoft Excel Worksheet Functions	7	3rd Jun 2008 08:44 PM
How to find a series of words and then changing formats	=?Utf-8?B?TW9sVG9t?=	Microsoft Word Document Management	4	13th Dec 2005 03:05 PM
Is it possible to do a Find/Replace on Conditional Formats?	ANDRE.TASSEL@ELECTROLUX.CO.UK	Microsoft Excel Worksheet Functions	2	21st Oct 2005 01:33 PM
Macro to Find Cells with Conditional Formats	John Franklin	Microsoft Excel Programming	6	1st Sep 2004 11:53 PM