"Right" & "Left" If statment

AVT_SMM

New member
Joined
Mar 9, 2015
Messages
7
Reaction score
0
Points
0
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
604799
ABC123
60334192-447
603447ABC321
4032845-0099-00-340
403340123ABC
680515-10373-148
680148321ABC
 
MNO
5Part NumberDesired OutputFormula output
6604153-24105-799ABC123ABC123
760334192-447ABC321ABC321
84032845-0099-00-340123ABC123ABC
9680515-10373-148321ABC321ABC
10
11
12
13
14
15604799ABC123
16603447ABC321
17403340123ABC
18680148321ABC

Spreadsheet Formulas
CellFormula
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.
 
Back
Top