Search string for space before pattern

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
608
Office Version
  1. 365
Platform
  1. Windows
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","")))
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top