Finding a Keyword in a Cell

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

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
--
Gary's Student


"thekovinc" wrote:

>
> 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
>
>
> --
> thekovinc
> ------------------------------------------------------------------------
> thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
> View this thread: http://www.excelforum.com/showthread...hreadid=543368
>
>

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.

"thekovinc" <thekovinc.280hbn_1147969500.9227@excelforum-nospam.com> wrote
in message news:thekovinc.280hbn_1147969500.9227@excelforum-nospam.com...
>
> 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.
--
David Biddulph

Thank you very much!