Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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.


Check out our Excel Resources

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

Posted by Mark W. on October 24, 2001 1:56 PM
=IF(ISNUMBER(SEARCH("*dog*",A1)),"dog","")


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

Posted by Chris Rock on October 24, 2001 2:00 PM
THANK YOU!!! I wish I'd thought of that!


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

Posted by Mark W. on October 24, 2001 2:02 PM
=IF(ISNUMBER(SEARCH("dog",A1)),"dog","")


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

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)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.