Wildcard in an If statement

[Amileaux]

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

 

[phantom1975]

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?

 

[Aladin Akyurek]

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")

Aladin

 

[phantom1975]

Your learn something new everyday......

Great idea Aladin.

 

[Amileaux]

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

 

[Tizoxic]

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.

 

[Matty]

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

 

[Matty]

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

Matty

 

[prabby25101981]

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")

 

[prabby25101981]

Or probably this - Array will be a better option -

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