# Wildcard in an If statement

This is a discussion on Wildcard in an If statement within the Excel Questions forums, part of the Question Forums category; Does a wildcard (* or ?) work in an if statement? For example in A1 I have the word "nextlevel" ...

1. Does a wildcard (* or ?) work in an if statement? For example in A1 I have the word "nextlevel" (wo the ""). In A2 I have: If(A1="nex*","Ok","No"). I have even tried If(A1="=nex*","ok","no"). In both instances I get "no", when I should be getting "ok". I figured out a work around using the left function - but still think the if statement should have worked.

A related question, when would I use "nex*" versus "=nex*"?

Thank you. Marie

2. WELCOME TO THE BOARD!

I don't know of a wildcard, but this would work:

=IF(LEFT(A1,3)="NEX","OK","NO")

This will look at the 3 leftmost letters of A1. If they are NEX, then you will get OK, otherwise NO.

Does this help?

3. On 2002-11-04 16:14, Amileaux wrote:
Does a wildcard (* or ?) work in an if statement? For example in A1 I have the word "nextlevel" (wo the ""). In A2 I have: If(A1="nex*","Ok","No"). I have even tried If(A1="=nex*","ok","no"). In both instances I get "no", when I should be getting "ok". I figured out a work around using the left function - but still think the if statement should have worked.

A related question, when would I use "nex*" versus "=nex*"?

Thank you. Marie
Marie,

You can use...

=IF(ISNUMBER(FIND("nex",A1)),"ok","no")

which uses case-sensitive FIND. Otherwise, substitute SEARCH for FIND.

You can of course put "nex", the substring to look for in a cell of its own, e.g., in C1 and use...

=IF(ISNUMBER(FIND(C1,A1)),"ok","no")

4. Your learn something new everyday......

5. Thanks! Had to try to "isnumber" one to see "why" - great idea. I take it that a simple if statment will not work. Thanks again. Marie

6. ## Re: Wildcard in an If statement

Does anyone know a way to incorporate nesting into this equation? In column A I have a list of product descriptions, and in Colomn B I'm tryinig to put an "X" if certain prases are found in the cell next to it in column A. I've tried to incorporate the OR function into this but it isn't working. For instance with the above example, I would have done:

=IF(ISNUMBER(FIND(OR("Nex","Big","Red"),A1)),"OK","NO")

It doesn't seem like the OR function can be used with the FIND function no matter how I order the function. Anyone have any ideas? thanks.

7. ## Re: Wildcard in an If statement

Welcome to the Board...

You'd have been better opening a new thread for this, but try:

=IF(OR(ISNUMBER(SEARCH({"Nex","Big","Red"},A1))),"OK","NO")

Matty

8. ## Re: Wildcard in an If statement

Also, be aware that SEARCH is not case sensitive, whereas FIND is. Change to suit.

Matty

9. ## Re: Wildcard in an If statement

I have made some changes to your formula. The correct one will be this -

=IF(OR(ISNUMBER(FIND("Nex",A1)),ISNUMBER(FIND("Big",A1)),ISNUMBER(FIND("Red",A1))),"OK","No")

10. ## Re: Wildcard in an If statement

Or probably this - Array will be a better option -

=IF(OR(ISNUMBER(FIND({"Nex","Big","Red"},A1))),"OK","No")

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•