Find string after 5 spaces and before a pattern

[rex759]

Hello,
I am not sure this is possible, but I am looking for a formula to show the string after 5 spaces from the left up to the space before a pattern. The pattern is a number that will always have a 2-digit decimal place and I would want to show prior to the space before it.

For example,
1357 09-013 1 10605 1234567890 1x16 WINDING BROOK-4.4 52.45 123456789 32 ____ = 1x16 WINDING BROOK-4.4
3504 02-024 1 10884 0000zxcas0 3/0 STRANDED THHN BLACK 4.65 320560 24 ____ = 3/0 STRANDED THHN BLACK

I was using the formula below which worked some of the time but the amount of spaces between words was not consistent.

=TRIM(MID(SUBSTITUTE(B21," ",REPT(" ",99)),6*99-98,99)) &" "& TRIM(MID(SUBSTITUTE(B21," ",REPT(" ",99)),7*99-98,99))&" "& TRIM(MID(SUBSTITUTE(B21," ",REPT(" ",99)),8*99-98,99))

Any help would be appreciated

 

[DanteAmor]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have this monstrous formula. But surely someone else will be able to find a simpler formula. But update your Account details

varios 22feb2021.xlsm
	A	B
1
2	1357 09-013 1 10605 1234567890 1x16 WINDING BROOK-4.4 52.45 123456789 32 ____	1x16 WINDING BROOK-4.4
3	3504 02-024 1 10884 0000zxcas0 3/0 STRANDED THHN BLACK 4.65 320560 24 ____	3/0 STRANDED THHN BLACK
Hoja6

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(LEFT(SUBSTITUTE(LEFT( TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9)),SEARCH("?.?? ", TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9))))," ",REPT(" ",99)),99*(LEN(LEFT( TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9)),SEARCH("?.?? ", TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9)))))-LEN(SUBSTITUTE(LEFT( TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9)),SEARCH("?.?? ", TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),99*5,9^9))))," ","")))))

 

[DanteAmor]

Here is a shorter formula, check if it works for all your texts.

varios 22feb2021.xlsm
	A	B
1
2	1357 09-013 1 10605 1234567890 1x16 WINDING BROOK-4.4 52.45 123456789 32 ____	1x16 WINDING BROOK-4.4
3	3504 02-024 1 10884 0000zxcas0 3/0 STRANDED THHN BLACK 4.65 320560 24 ____	3/0 STRANDED THHN BLACK
Hoja6

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(MID(SUBSTITUTE(LEFT(A2,SEARCH("???.?? ",A2))," ",REPT(" ",99)),99*5,9^9))

 

[bebo021999]

Hi Dante
With this statement from OP "The pattern is a number that will always have a 2-digit decimal place"
how formula cound be with data like this:
xxxxxx AA.BB xxxx 52.45
???

 

[Rick Rothstein]

If there are always 3 spaces after the number with 2 decimal places (like your 2 examples show), then this formula should work even if text like bebo021999 posted is part of your values...

=MID(LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",300)),1200)))-1),FIND("|",SUBSTITUTE(A2," ","|",5))+1,99)

 

[rex759]

Thank you everyone for quick replies and account information.

Dante’s formula:

=TRIM(MID(SUBSTITUTE(LEFT(B15,SEARCH("???.?? ",B15))," ",REPT(" ",99)),99*5,9^9))

Worked most of the time except in examples like this:
1101 11-999 1 10049 z000q52967 XCX SHEET 1/2X48X8 REV 109.07 00000029q0 15 ____

Which produced these results adding a 1 at the end.
XCX SHEET 1/2X48X8 REV 1

Rick’s formula worked in all scenarios.

Thanks again

 

[DanteAmor]

Dante’s formula:

=TRIM(MID(SUBSTITUTE(LEFT(B15,SEARCH("???.?? ",B15))," ",REPT(" ",99)),99*5,9^9))

Worked most of the time except in examples like this:
1101 11-999 1 10049 z000q52967 XCX SHEET 1/2X48X8 REV 109.07 00000029q0 15 ____

That's right, that formula doesn't work.
But the formula in post #2 does work . Although of course, Rick's is magnificent ?.