+ Reply to Thread
Results 1 to 7 of 7

Need If formula that looks for certain starting letters

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    9

    Need If formula that looks for certain starting letters

    I have a list of parts numbers that I need to check is they are discontinued or not.

    Example:

    66122
    66123
    66042
    DISC21010
    66080
    DISC20004
    008Z

    I need formula to determine if the values in the cell start with DISC, if so to place in an other cell next to it "Discontinued" and if not "OK"
    I am having issue with using wild cards and not sure how?
    I would appreciate it if anyone can help

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Need If formula that looks for certain starting letters

    Try

    =IF(LEFT(A1,4)="DISC","Discontinued","OK")

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need If formula that looks for certain starting letters

    Try this...

    =IF(LEFT(A2,4)="DISC","Discontinued","OK")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-07-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Need If formula that looks for certain starting letters

    Thanks that did it!!!

    Now can this be used with any other value just by changing the "DISC" or how would I do it if the DISC was at the end of the part number?

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Need If formula that looks for certain starting letters

    Glad to help..

    For the first question, we would need to see some examples.
    Would you want to put the value you're searching for in a cell?
    And what would the resulting value be? would it still be Discontinued?

    For checking the end, just change the LEFT to RIGHT
    =IF(RIGHT(A1,4)="DISC","Discontinued","OK")

  6. #6
    Registered User
    Join Date
    05-07-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Need If formula that looks for certain starting letters

    Quote Originally Posted by Jonmo1 View Post
    Glad to help..

    For the first question, we would need to see some examples.
    Would you want to put the value you're searching for in a cell?
    And what would the resulting value be? would it still be Discontinued?
    Lets say in the list below we had the following

    Example:

    DISS66122
    66123
    DISK66042
    DISC21010
    DIS66080
    DISC20004
    DISS008Z

    I would need to have anything with DISS or DISC marked the same as the 1st example, Discontinued or OK

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Need If formula that looks for certain starting letters

    Try

    =IF(OR(LEFT(A1,4)={"DISC","DISS"}),"Discontinued","OK")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1