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!
