Page 1 of 3 123 LastLast
Results 1 to 10 of 24

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. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default

    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. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    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?
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,863

    Default

    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

  4. #4
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    Your learn something new everyday......

    Great idea Aladin.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  5. #5
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default

    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. #6
    New Member
    Join Date
    Sep 2010
    Posts
    5

    Default 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. #7
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,227

    Default 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. #8
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,227

    Default Re: Wildcard in an If statement

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

    Matty

  9. #9
    Board Regular
    Join Date
    Jul 2010
    Location
    New Delhi
    Posts
    348

    Default 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")
    Hope This Helps !
    Prabby
    --------------------
    WannaB MrExcel MVP
    - tRYING tO fIGURE oUT hOW !

  10. #10
    Board Regular
    Join Date
    Jul 2010
    Location
    New Delhi
    Posts
    348

    Default 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")
    Hope This Helps !
    Prabby
    --------------------
    WannaB MrExcel MVP
    - tRYING tO fIGURE oUT hOW !

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com