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

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" wrote
in message ...

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!


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=543368