Results 1 to 3 of 3

Thread: "Right" & "Left" If statment

  1. #1

    "Right" & "Left" If statment



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I am in need of a formula that will be able to identify the Right most and Left most characters to produce a defined output. A standard If statement limits me to 6 defined outputs, but I will have an undefined number of outputs. My goal is to be able to define the output based on a set table. Then when a new Part Number is entered, it will result in the defined output based on the Most Right and Most Left 3 characters.
    Part Number
    Left Most
    Right Most
    Defined Output
    604153-24105-799
    604 799
    ABC123
    60334192-447
    603 447 ABC321
    4032845-0099-00-340
    403 340 123ABC
    680515-10373-148
    680 148 321ABC

  2. #2
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas, no VBA macro.
    http://www.mediafire.com/view/4vnu63t05uaq09w/08_11_15a.xlsx


  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,551
    Articles
    0
    Excel Version
    365
    M N O
    5 Part Number Desired Output Formula output
    6 604153-24105-799 ABC123 ABC123
    7 60334192-447 ABC321 ABC321
    8 4032845-0099-00-340 123ABC 123ABC
    9 680515-10373-148 321ABC 321ABC
    10
    11
    12
    13
    14
    15 604799 ABC123
    16 603447 ABC321
    17 403340 123ABC
    18 680148 321ABC

    Spreadsheet Formulas
    Cell Formula
    O6 =VLOOKUP(LEFT(M6,3)&RIGHT(M6,3),$M$15:$N$18,2,FALSE)
    O7 =VLOOKUP(LEFT(M7,3)&RIGHT(M7,3),$M$15:$N$18,2,FALSE)
    O8 =VLOOKUP(LEFT(M8,3)&RIGHT(M8,3),$M$15:$N$18,2,FALSE)
    O9 =VLOOKUP(LEFT(M9,3)&RIGHT(M9,3),$M$15:$N$18,2,FALSE)


    Excel tables to the web >> Excel Jeanie HTML 4

    There's a small lookup table at M15:N18 which can be expanded.
    One formula in O6 copied down.

Posting Permissions

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