Hello,
I was able to get the results I need by adding Helper Columns but thought someone might have a cleaner solution. Columns C & D have the results I want. My goal is to show the letter (always a single digit) before the second space before this pattern "-???-" is found.
Column B places that letter (if found) either as the first or last position of the string. Then I can pull the results in column C & D.
Any help is appreciated
I was able to get the results I need by adding Helper Columns but thought someone might have a cleaner solution. Columns C & D have the results I want. My goal is to show the letter (always a single digit) before the second space before this pattern "-???-" is found.
Column B places that letter (if found) either as the first or last position of the string. Then I can pull the results in column C & D.
Any help is appreciated
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Inventory | Helper Column | Helper 1 | Helper 2 | ||
2 | 03/02/2020 X0098-1234567 VVV WIDGITS I 25 0001-123-723 BUILT-IN APP 1.0 $000.01 | 03/02/2020 X0098-1234567 VVV WIDGITS I | I | |||
3 | 07/07/2019 X0098-249896 VVV DILLER M 01 0200-898-501 EXTERIORDOOR 1.0 $00.00 | 07/07/2019 X0098-249896 VVV DILLER M | ||||
4 | 07/07/2019 X0098-249926 VVV MCGOWAN M 01 0030-960-858 LAMINTE TIER 1.0 $00.00 | 07/07/2019 X0098-249926 VVV MCGOWAN M | ||||
5 | 07/10/2019 X0098-250500 VVV TEST I 01 0000-100-276 iii XXX 1.0 $0.00 | 07/10/2019 X0098-250500 VVV TEST I | I | |||
6 | 02/25/2020 X0098-251135 VXW WEDGE | 02/25/2020 X0098-251135 VXW WEDGE | ||||
7 | I 01 1003-421-817 B&S LLLL 1.0 $09.00 | I 01 1003-421-817 B&S LLLL | I | |||
8 | S 0202 1003-010-296 BRXZM1 Cell | S 0202 1003-010-296 BRXZM1 Cell | S | |||
9 | Shade 6.0 $0.00 | Shade 6.0 $0.00 | ||||
10 | 08/09/2019 X0098-253777 VVV OOO R 0101 0001-040-145 0343D-26-12 Shoot | 08/09/2019 X0098-253777 VVV OOO R | R | |||
11 | Out - Color Fedora Gre 124.33 $000.13 | Out - Color Fedora Gre | ||||
12 | 07/31/2019 X0098-254947 VVV XYZ WORKS | 07/31/2019 X0098-254947 VVV XYZ WORKS | ||||
13 | S 0101 0002-805-450 PPG73-41005 3.0 $0.80 | S 0101 0002-805-450 PPG73-41005 3.0 | S | |||
14 | S 0102 1222-800-458 PPG73-51005 2.0 $000.40 | S 0102 1222-800-458 PPG73-51005 2.0 | S | |||
15 | S 0203 0000-101-686 PPG83-11005 4.0 $000.80 | S 0203 0000-101-686 PPG83-11005 4.0 | S | |||
16 | ||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B15 | B2 | =TRIM(LEFT(SUBSTITUTE(A2, " ", REPT(" ",255),5), 255)) |
C2:C15 | C2 | =IF(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="s","S",IF(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="i","I",IF(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="R","R",""))) |
D2:D15 | D2 | =IF(TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="I","I",IF(TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="R","R",IF(TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2)))="S","S",""))) |