# Thread: "Right" & "Left" If statment

1. ## "Right" & "Left" If statment

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. 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.  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

 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
•