Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting based on text?
Is it possible to implement conditional formatting based on text contained in a cell? Example, user wants to be able to highlight all the cells in a spreadsheet for a given entity (which will change all the time) so I'm looking for something like "cell contains xyz" -- but as far as I can tell, it works only with numeric values. Am I trying to do something that is impossible? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting based on text?
Until somebody suggests something a little more polished, use a formula like:
=LEN(E4)=LEN(SUBSTITUTE(E4,"Smith","")) "KarenH" wrote: Is it possible to implement conditional formatting based on text contained in a cell? Example, user wants to be able to highlight all the cells in a spreadsheet for a given entity (which will change all the time) so I'm looking for something like "cell contains xyz" -- but as far as I can tell, it works only with numeric values. Am I trying to do something that is impossible? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting based on text?
I think you can do conditional formatting, select the "between"
option, and put a in the first cell and z in the second. I just tested it out and it seems to work even if the cell has multiple letters. Once I type a number in it the format goes away. Is that what you were looking for? -Steven On Feb 21, 4:10 pm, KarenH wrote: Is it possible to implement conditional formatting based on text contained in a cell? Example, user wants to be able to highlight all the cells in a spreadsheet for a given entity (which will change all the time) so I'm looking for something like "cell contains xyz" -- but as far as I can tell, it works only with numeric values. Am I trying to do something that is impossible? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting based on text?
Select the whole range, or even the whole row(s) or column(s) where you want
this conditional formatting, then do Format/Conditional Formatting. In the upper let corner of the dialog box, change "Cell Value Is" to "Formula Is". Now it expects a formula which evaluates to TRUE or FALSE (although non-zero and zero will do). Any cell references in the formula are subject to absolute and relative addressing, so if you chose all of Column A, the formula should be: =FIND("xyz",A1) 0 Then, if you select cell A2 and do Format/Conditional Formatting, you'll find: =FIND("xyz",A2) 0 and so on for A3, A4, etc. -- --Andy Smith "KarenH" wrote: Is it possible to implement conditional formatting based on text contained in a cell? Example, user wants to be able to highlight all the cells in a spreadsheet for a given entity (which will change all the time) so I'm looking for something like "cell contains xyz" -- but as far as I can tell, it works only with numeric values. Am I trying to do something that is impossible? Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting based on text?
I think Andy's solution is the way to go. Only 2 additions:
1) since the OP mentioned that the text changes all the time, it would be better not to hard code it into the condition. Assuming that D1 contains the text the formula would be =FIND($D$1,A1) 0 or just =FIND($D$1,A1) 2) If the texts should match exactly the formula =$D$1=A1 could be used (first '=' identifies the following stuff as a formula and D1=A1 is a valid formula, resulting either in TRUE or FALSE) Joerg "Andy Smith" wrote in message ... Select the whole range, or even the whole row(s) or column(s) where you want this conditional formatting, then do Format/Conditional Formatting. In the upper let corner of the dialog box, change "Cell Value Is" to "Formula Is". Now it expects a formula which evaluates to TRUE or FALSE (although non-zero and zero will do). Any cell references in the formula are subject to absolute and relative addressing, so if you chose all of Column A, the formula should be: =FIND("xyz",A1) 0 Then, if you select cell A2 and do Format/Conditional Formatting, you'll find: =FIND("xyz",A2) 0 and so on for A3, A4, etc. -- --Andy Smith "KarenH" wrote: Is it possible to implement conditional formatting based on text contained in a cell? Example, user wants to be able to highlight all the cells in a spreadsheet for a given entity (which will change all the time) so I'm looking for something like "cell contains xyz" -- but as far as I can tell, it works only with numeric values. Am I trying to do something that is impossible? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Conditional formatting based on text within a formula | Excel Worksheet Functions | |||
Conditional formatting of an entire row based on a cell text entry | Excel Discussion (Misc queries) | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
Conditional formatting based on text | Excel Worksheet Functions |