Finding a Keyword in a Cell

[thekovinc]

If I have a group of cells that have different text strings in them
(differing in length as well), is there any easy way to write a formula
that will tell if the cells have a certain word at any point in the
string?

i.e. if I am looking to flag any cells with the word "exempt" in them,
how could you write a formula that would pick up if "exempt" were in
the beginning, end, or somewhere in the middle of the cell?

Thanks,
Nick

 

[Guest]

Select cell A1 and pull-down:
Format > Conditional formating... > Equation is >
=ISNUMBER(SEARCH("exempt",A1)) and pick a nice format

Then copy the cell and paste/special format over the cells you want to
investigate

 

[Dave O]

The SEARCH and FIND functions will do this for you- SEARCH is not case
sensitive, while FIND is case sensitive. In your example if the word
"exempt" does not appear in the cell, both SEARCH and FIND will return
an error message, so you may need to nest them into an IF statement:
=IF(ISNUMBER(SEARCH(A1,"exempt",1)),"Exempt","")
....to avoid the error.

 

[David Biddulph]

If I have a group of cells that have different text strings in them
(differing in length as well), is there any easy way to write a formula
that will tell if the cells have a certain word at any point in the
string?

i.e. if I am looking to flag any cells with the word "exempt" in them,
how could you write a formula that would pick up if "exempt" were in
the beginning, end, or somewhere in the middle of the cell?

=ISNUMBER(FIND("exempt",A1)) will return true or false and is case
sensitive.
=ISNUMBER(SEARCH("exempt",A1)) if you don't want it case sensitive.

 

[thekovinc]

Thank you very much!