Extract string after first space and numbers

[rex759]

Hello,
I’m looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits). The formula I’m using is not accurate since the number of words that appears between the first space to the first set of numbers changes.

Any help is appreciated

Excel Workbook
	A	B	C
1			Desired results
2	277 xxx spzrtmhk companies 12345678 231516990 2475.06 3199.57 23.19 street 1 739.00	xxx spzrtmhk companies12345678 231516990	xxx spzrtmhk companies 12345678
3	1528 dts powertrain comp inc 15981808 231516989 43.55 84.40 48.40 5.00	dts powertrain compinc 15981808	dts powertrain compinc 15981808
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),2*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),3*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),4*99-98,99))&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),5*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),6*99-98,99))
B3	=TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99)),2*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99)),3*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99)),4*99-98,99))&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99)),5*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99)),6*99-98,99))

 

[Austrada]

Try This:

=MID(A2,FIND(" ",A2)+1,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2,FIND(" ",A2))),FIND({1,2,3,4,5,6,7,8,9,0},A2,FIND(" ",A2))))-(FIND(" ",A2)+1))

 

[Rick Rothstein]

Hello,
I’m looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits).
Sheet2

	A	B	C
1			Desired results
2	277 xxx spzrtmhk companies 12345678 231516990 2475.06 3199.57 23.19 street 1 739.00	xxx spzrtmhk companies12345678 231516990	xxx spzrtmhk companies 12345678
3	1528 dts powertrain comp inc 15981808 231516989 43.55 84.40 48.40 5.00	dts powertrain compinc 15981808	dts powertrain compinc 15981808

<thead>
</thead><tbody>
</tbody>

Excel 2010

Could there ever be any numbers within the text (highlighted in red) between the first number (that you want to skip over) and the 8-digit number (that you want to keep)?

 

[rex759]

Thank you for the replies.
Austrada - You're very close! I want to include the 8-digit number that follows the text. Your formula cuts off just before it.

 

[rex759]

Thank you Rick for coming to my resuce once again.

To answer your question, no, there will not be any numbers in between the text and the 8-digit number I want to keep

 

[rex759]

I'm heading to bed guys, will look at this first thing in the AM. Thanks again!

 

[Rick Rothstein]

Thank you Rick for coming to my resuce once again.

To answer your question, no, there will not be any numbers in between the text and the 8-digit number I want to keep

Good, then this relatively "simple" formula should work for you...

=LEFT(MID(A1,FIND(" ",A1)+1,999),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND(" ",A1)+1,999)&"0123456789"))+8)

 

[István Hirsch]

Good, then this relatively "simple" formula should work for you...

=LEFT(MID(A1,FIND(" ",A1)+1,999),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND(" ",A1)+1,999)&"0123456789"))+8)

I think the end of the formula should be +7)

Give this formula a try too:

=MID(A2,FIND(" ",A2)+1,MIN(FIND(" "&{0,1,2,3,4,5,6,7,8,9},A2&" 0 1 2 3 4 5 6 7 8 9"))-FIND(" ",A2)+8)

 

[rex759]

Thank you both! The formulas are both working! As always, I appreciate your assistance.
Thanks again

 

[István Hirsch]

You are welcome.