Search string for space before pattern

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
530
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

Book1
ABCD
1InventoryHelper ColumnHelper 1Helper 2
203/02/2020 X0098-1234567 VVV WIDGITS I 25 0001-123-723 BUILT-IN APP 1.0 $000.0103/02/2020 X0098-1234567 VVV WIDGITS II 
307/07/2019 X0098-249896 VVV DILLER M 01 0200-898-501 EXTERIORDOOR 1.0 $00.0007/07/2019 X0098-249896 VVV DILLER M  
407/07/2019 X0098-249926 VVV MCGOWAN M 01 0030-960-858 LAMINTE TIER 1.0 $00.0007/07/2019 X0098-249926 VVV MCGOWAN M  
507/10/2019 X0098-250500 VVV TEST I 01 0000-100-276 iii XXX 1.0 $0.0007/10/2019 X0098-250500 VVV TEST II 
602/25/2020 X0098-251135 VXW WEDGE02/25/2020 X0098-251135 VXW WEDGE  
7I 01 1003-421-817 B&S LLLL 1.0 $09.00I 01 1003-421-817 B&S LLLL I
8S 0202 1003-010-296 BRXZM1 CellS 0202 1003-010-296 BRXZM1 Cell S
9Shade 6.0 $0.00Shade 6.0 $0.00  
1008/09/2019 X0098-253777 VVV OOO R 0101 0001-040-145 0343D-26-12 Shoot08/09/2019 X0098-253777 VVV OOO RR 
11Out - Color Fedora Gre 124.33 $000.13Out - Color Fedora Gre  
1207/31/2019 X0098-254947 VVV XYZ WORKS07/31/2019 X0098-254947 VVV XYZ WORKS  
13S 0101 0002-805-450 PPG73-41005 3.0 $0.80S 0101 0002-805-450 PPG73-41005 3.0 S
14S 0102 1222-800-458 PPG73-51005 2.0 $000.40S 0102 1222-800-458 PPG73-51005 2.0 S
15S 0203 0000-101-686 PPG83-11005 4.0 $000.80S 0203 0000-101-686 PPG83-11005 4.0 S
16
Sheet3
Cell Formulas
RangeFormula
B2:B15B2=TRIM(LEFT(SUBSTITUTE(A2, " ", REPT(" ",255),5), 255))
C2:C15C2=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:D15D2=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","")))
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
AB
1Inventory
203/02/2020 X0098-1234567 VVV WIDGITS I 25 0001-123-723 BUILT-IN APP 1.0 $000.01I
307/07/2019 X0098-249896 VVV DILLER M 01 0200-898-501 EXTERIORDOOR 1.0 $00.00M
407/07/2019 X0098-249926 VVV MCGOWAN M 01 0030-960-858 LAMINTE TIER 1.0 $00.00M
507/10/2019 X0098-250500 VVV TEST I 01 0000-100-276 iii XXX 1.0 $0.00I
602/25/2020 X0098-251135 VXW WEDGE 
7I 01 1003-421-817 B&S LLLL 1.0 $09.00I
8S 0202 1003-010-296 BRXZM1 CellS
9Shade 6.0 $0.00 
1008/09/2019 X0098-253777 VVV OOO R 0101 0001-040-145 0343D-26-12 ShootR
11Out - Color Fedora Gre 124.33 $000.13 
1207/31/2019 X0098-254947 VVV XYZ WORKS 
13S 0101 0002-805-450 PPG73-41005 3.0 $0.80S
14S 0102 1222-800-458 PPG73-51005 2.0 $000.40S
15S 0203 0000-101-686 PPG83-11005 4.0 $000.80S
Main
Cell Formulas
RangeFormula
B2:B15B2=IFERROR(TRIM(LEFT(RIGHT(SUBSTITUTE(REPLACE(A2,SEARCH("-???-",A2)-4,100,"")," ",REPT(" ",100)),300),100)),"")
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
530
Wow, perfect as always! It challenging for me to combine functions like this. Can you explain the priority of which function is doing its thing first? I assume the Search does
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
The easiest way to see the order & what each part does, is select the cell with the formula & on the formula tab click "Evaluate Formula" & then click "Evaluate" the underlined portion is the next part of the formula to get evaluated.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,341
Messages
5,624,111
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top