An IF statements that operates if a cell CONTAINS a word...

Posted by Chris Rock on October 24, 2001 1:53 PM

Is it possible to write an IF statement that looks at a string of text, and will perform an action if the string CONTAINS a certain word?

For example. IF this statement in cell A1 contains the word "dog", write "dog". Otherwise, write nothing.

"I went to the pet store and I bought some dog food."

I can't put =IF(A1=dog,"dog","") because A1 doesn't EQUAL "dog", it CONTAINS "dog".

Or is there a better way to do this?

Thanks.

Posted by Mark W. on October 24, 2001 1:56 PM

=IF(ISNUMBER(SEARCH("*dog*",A1)),"dog","")

Posted by Chris Rock on October 24, 2001 2:00 PM

THANK YOU!!! I wish I'd thought of that!

Posted by Mark W. on October 24, 2001 2:02 PM

BTW, you can get by without wildcards ("*") too...

=IF(ISNUMBER(SEARCH("dog",A1)),"dog","")

Posted by Kevin on October 24, 2001 4:24 PM

I think the easiest way to obtain the results you are looking for would be this:

Note the column header must = F10

=IF($F10=J$9,+F10)