Wild Cards with IF stmt

K

KarenF

I have a simple IF stmt that I can't get to recognize wild cards.

=IF(X1="Food*","yes","no")

I want it to return a yes for Food, Food Dept, Food Services - Intl, etc.
In short, anything that starts with food. Shouldn't it be easy?

I have a macro that imports from another spreadsheet. Once I get a formula
to work, I record that step then paste it to my macro and adjust the code so
it compiles, then add steps to copy the formula to the rest of the rows.

Which is why I can't simply filter on "contains food."

If I can get this simple function to work then I'm confident I can apply it
to my complex formula. But, did I read somewhere that IF stmts don't support
wild cards?

KarenF
 
R

Ron Coderre

Here you go:

=IF(COUNTIF(X1,"Food*"),"yes","no")


Does that help?
***********
Regards,
Ron

XL2003, WinXP
 
T

T. Valko

If "Food" is *always* at the beginning of the string:

=IF(LEFT(X1,4)="Food","Yes,"No")

If "Food" might be anywhere within the string:

=IF(COUNTIF(X1,"*Food*"),"Yes","No")
 
K

KarenF

ALL these solutions work beautifully! I never thought of using CountIF in a
a plain IF stmt (I'm not trying to count anything), but these are exactly
what I need. Thanks to all 3 of you ...

KarenF
 
T

T. Valko

You're welcome.

To directly answer your question:
did I read somewhere that IF stmts don't support wild cards?

That is correct. In the formulas we suggested it's not the IF function
that's using the wildcard, it's the COUNTIF function.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top