I am trying to do conditional formatting based on whether or not a cell contains "IOU". The IOU will be with other text and not always in the same position.
How would I construct the formula to resolve to true/false based on if the cell contains IOU?
Test if specific text is contained within a cell
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Test if specific text is contained within a cell
There's probably a far more elegant way to do this, but the formula
=SEARCH("IOU",UPPER(E10),1)<LEN(E10)
works for me
(assuming that you want a case-insensitive search and the text is in cell E10)
=SEARCH("IOU",UPPER(E10),1)<LEN(E10)
works for me
(assuming that you want a case-insensitive search and the text is in cell E10)
StuartR
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Test if specific text is contained within a cell
You need conditional formatting with a formula - select 'Formula Is' in the first dropdown in the Conditional Formatting dialog (Excel 2003 or before), or select ''Use a formula to determine which cells to format' in the New Formatting Rule dialog (Excel 2007 or later).
Let's say the active cell within the selection that you want to format is A1.
The formula would be
=NOT(ISERROR(SEARCH("IOU",A1)))
if you want to format cells that contain "IOU", or
=ISERROR(SEARCH("IOU",A1))
if you want to format cells that don't contain "IOU".
The SEARCH function is not case-sensitive, so it'll detect IOU, ioU and iou. If you want to detect only IOU, use FIND instead of SEARCH.
Let's say the active cell within the selection that you want to format is A1.
The formula would be
=NOT(ISERROR(SEARCH("IOU",A1)))
if you want to format cells that contain "IOU", or
=ISERROR(SEARCH("IOU",A1))
if you want to format cells that don't contain "IOU".
The SEARCH function is not case-sensitive, so it'll detect IOU, ioU and iou. If you want to detect only IOU, use FIND instead of SEARCH.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Test if specific text is contained within a cell
to Hans: I entered the formula as you suggested =NOT(ISERROR(SEARCH("IOU",A1))) and it resolves to TRUE yet the formatting does not show (I used a color fill). Here are the dialog boxes (with the formula pointing to the actual cells). Oddly enough, I did a CF for cell AF223 with the formula =NOT(ISERROR(SEARCH("IOU",AE223))) and the fill did show. However, the fill should have shown in A223:AE223
You do not have the required permissions to view the files attached to this post.
Don
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Test if specific text is contained within a cell
Try a simpler solution:
Click Conditional Formatting > Highlight Cell Rules | Text That Contains...
Enter IOU in the box.
Select Custom Format... from the format dropdown.
Specify the format you want, then OK your way out.
Click Conditional Formatting > Highlight Cell Rules | Text That Contains...
Enter IOU in the box.
Select Custom Format... from the format dropdown.
Specify the format you want, then OK your way out.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Test if specific text is contained within a cell
That did it however, it only highlights cells in column AE (where the text is) and that column is not visible unless I scroll over to it. Not a problem, though because I did CF on a column to the far left using =NOT(ISERROR(SEARCH("IOU",Ae2))) and it works.
Wonder why the entire rows did not CF as I originally tried to do.
Wonder why the entire rows did not CF as I originally tried to do.
Don
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Test if specific text is contained within a cell
Ah, I see the cause of the misunderstanding now. You want to highlight all cells in a row if the cell in column AE contains "IOU". I thought you wanted to highlight a cell if that cell itself contains "IOU".
You'll have to go back to the original idea (using a formula):
- Select A2:AE398.
- Click Conditional Formatting > Manage Rules...
- Select the rule that formats cells that contain the text IUO.
- Select 'Use a formula to determine which cells to format'.
- Enter this slightly changed formula:
=NOT(ISERROR(SEARCH("IOU",$AE2)))
- Click OK.
You'll have to go back to the original idea (using a formula):
- Select A2:AE398.
- Click Conditional Formatting > Manage Rules...
- Select the rule that formats cells that contain the text IUO.
- Select 'Use a formula to determine which cells to format'.
- Enter this slightly changed formula:
=NOT(ISERROR(SEARCH("IOU",$AE2)))
- Click OK.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA