Wildcard in an If statement

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Wildcard in an If statement

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,443
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,628
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,628
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 !

User Tag List

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