| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I isolate the last two words in a text string? | Excel Discussion (Misc queries) | |||
| find a string of nth occurance & pick next 3 words | Excel Worksheet Functions | |||
| i need help creating a function that will extract words from a string of text | Excel Worksheet Functions | |||
| Extracting the last set of words from a text string | Excel Worksheet Functions | |||
| How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
Linear Mode
