+ Reply to Thread
Results 1 to 2 of 2

If then formula to ignore text after number

  1. #1
    Registered User
    Join Date
    02-10-2012
    Location
    Duluth, MN
    MS-Off Ver
    Excel 2007
    Posts
    12

    If then formula to ignore text after number

    I have a formula that displays a model number in a cell according to a serial number in another.

    This formula works fine, except that I occasionally want to be able to have other text after the serial number for descriptive and reference purposes.

    I could make a longer formula, but I am almost up to 64 variations of "IF" already.

    Should I try a different approach, or is there an add-in to my formula I could use to make my current formula work like I explained?

    Please see the following:

    "=IF(LEFT(A5,3)="MSD","MSD",IF(A5>=2500301,"GXP2500",IF(A5>=1500301,"GXP1500",IF(A5>=1400301,"GXP1400",IF(A5>=1400001,"GMS1400",IF(A5>=1200103,"GXP1200",IF(A5>=1200003,"GDP1200",IF(A5>=1000301,"GXP1000",IF(A5>=1000001,"GMS1000",IF(A5>=990301,"GXP990",IF(A5>=950001,"GC950",IF(A5>=700301,"GXP700",IF(A5>=700001,"GMS700",IF(A5>=650001,"GDP650",IF(A5>=620101,"LXP600",IF(A5>=610110,"GCP610",IF(A5>=600301,"GXP660",IF(A5>=520101,"LXP500",IF(A5>=500301,"GXP500",IF(A5>=500001,"GMS500",IF(A5>=450001,"GDP450",IF(A5>=440301,"GXP440",IF(A5>=420101,"LXP400",IF(A5>=410151,"GCP410",IF(A5>=400301,"GXP400",IF(A5>=400001,"GMS400",IF(A5>=320101,"LXP300",IF(A5>=310101,"GDR300",IF(A5>=300301,"GXP300",IF(A5>=300001,"GMS300",IF(A5>=250100,"GSD250",IF(A5>=220101,"LXP200",IF(A5>=210101,"GDR200",IF(A5>=201101,"GSG200",IF(A5>=200301,"GXP200",IF(A5>=170100,"GSD170",IF(A5>=152101,"GSG150",IF(A5>=125101,"GSG125",IF(A5>=102101,"GSG100",IF(A5>=97101,"GCC90",IF(A5>=96101,"GLS90",IF(A5>=94101,"GMP90",IF(A5>=92101,"GRS90",IF(A5>=77101,"GCC70",IF(A5>=76101,"GLS70",IF(A5>=75101,"GSG75",IF(A5>=74101,"GMP70",IF(A5>=72101,"GRS70",IF(A5>=70101,"GSD70",IF(A5>=57101,"GCC50",IF(A5>=56101,"GLS50",IF(A5>=50101,"GSD50",IF(A5>=40101,"GSD40",IF(A5>=30101,"GSD30",IF(A5>=15001,"GVP15",IF(A5>=7001,"GVP07","ADD ATTACHMENT"))))))))))))))))))))))))))))))))))))))))))))))))))))))))"

    For example, sometimes I would need to type "700305R" or "700305-Rebuild" or "700305-Rebuild2" or "700305-Conversion". In any of these four instances, the letters after "700305" do not change the fact that the model is still GXP700.

    Thank you for looking.

    Jake

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: If then formula to ignore text after number

    Instead of a thousand IF statements, just use the LOOKUP formula..with ascending order of values in the array

    =IF(LEFT(A5,3)="MSD",if(A5<7000,"ADD ATTACHMENT",LOOKUP({7001,15001,30101,..........},{"GVP07","GVP15","GSD30",............})))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ 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