Test if specific text is contained within a cell

[dasadler]

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?

[StuartR]

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)

[HansV]

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.

[StuartR]

Thanks for the education

[dasadler]

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

[HansV]

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.

CF.png

[dasadler]

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.

[HansV]

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.

[dasadler]

Perfect! Thank you.