Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wild Card use in Excel (2003 SP1)

    I'm trying to use =IF(A1="*TBD*,"T","F") to find any entry with the letters "TBD" (could be additional characters to the left or right ) but it isn't working for me. I've also tried other variations using ~ and ? with no success. The entries in A1 will be something like "TBD - open job 1" or "Open Job 1 - TBD." Any suggestions?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wild Card use in Excel (2003 SP1)

    You could use this formula:
    <code>
    =ISNUMBER(SEARCH("TBD",A1))
    </code>
    This will return TRUE or FALSE. If you prefer "T" or "F", use
    <code>
    =IF(ISNUMBER(SEARCH("TBD",A1)),"T","F")
    </code>
    The SEARCH function returns a number indicating the position of the search string if found, and the error value #VALUE if the search string does not occur. ISNUMBER is used to convert the result of SEARCH to TRUE or FALSE.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wild Card use in Excel (2003 SP1)

    Wild cards do not work with the comparison operators. You can use something like:

    <code>
    =IF(ISERROR(SEARCH("TBD",A1)),"F","T")
    </code>

    If you want it to be case sensitive, use FIND instead of SEARCH.
    Legare Coleman

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Wild Card use in Excel (2003 SP1)

    Just as an alternative, you can also use:
    <code>=IF(COUNTIF(A1,"*TBD*),"T","F")</code>
    which is not case sensitive.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wild Card use in Excel (2003 SP1)

    Thanks - what if I want to use it in an IF(AND statement; i.e. if A1 has *TBD* in it and B1 = TBD, "OK". If B1 has anything else "ERROR"

    Thanks again.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wild Card use in Excel (2003 SP1)

    I think you are missing a double quote in that formula.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wild Card use in Excel (2003 SP1)

    <code>
    =IF(OR(ISERROR(SEARCH("TBD",A1)),B1<>"TBD"),"ERROR ","OK")
    </code>
    Legare Coleman

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Wild Card use in Excel (2003 SP1)

    <P ID="edit" class=small>(Edited by rory on 26-Jun-07 09:23. corrected typo - thanks, Legare!)</P>Try:
    <code>=IF(AND(COUNTIF(A1,"*TBD*"),B1="TBD"),"OK"," ERROR")</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Wild Card use in Excel (2003 SP1)

    Thanks - I have corrected it. (It was late and I got carried away with the backspace key... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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