MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Wildcards in IF Functions


Posted by Marc on March 08, 2001 12:37 PM

Hi,

Is it possible to run IF functions using wildcards?

Let's say I have a column listing 500 different kinds of foods and I want the word "Yes" to appear next to every entry that has the word "rum" anywhere in the text (rum raisin, buttered rum, etc). How can I run an IF function that will do this? I tried using the "*" in the formula, but it does not work.

Please help. Thanks!


Posted by Aladin Akyurek on March 08, 2001 12:44 PM

Try

=IF(SEARCH("rum",A1)>0,"Yes","No")

Posted by Mark W. on March 08, 2001 12:50 PM

There a lots of ways... here are some:

=IF(ISNUMBER(SEARCH("rum",A1)),"Yes","No")

or

=ISNUMBER(MATCH("*rum*",A1,0))+0

The latter case will return a 1 for TRUE or a 0
for FALSE. If you use this approach you can then
format the cell containing the formula as
[=1]"Yes";[=0]"No". As you can see this approach
makes for a much simpler formula. I highly
recommend the you grow accustom to using 1/0 as
boolean values rather then Y/N, Yes/No, etc., and
then you can get by without using IF().

Posted by Mark W. on March 08, 2001 12:51 PM

Aladin, this formula will return #VALUE! if "rum"
isn't a substring.

Posted by Aladin Akyurek on March 08, 2001 2:31 PM

Must change from too much ESPRESSO to a decent sort of RUM.

Posted by Marc on March 12, 2001 6:15 AM

There a lots of ways... here are some:


Thanks for such a thorough answer Mark!
I see what you mean about the boolean values. I'll that that advice!