Extract second to last number from string

[DerekWooley]

Hello, what formula could I use to extract the second to last and third to last numbers from the below string separately? I want to pull 4.410 out and then the 5.007 out.

704938 1-6/9" Dia. 12L14 Metal Row Hard 1 5.007 4.410 4.4330

the string is located in cell A10.

I extracted the last number (4.430)using : =RIGHT(A10,LEN(A10)-FIND("*",SUBSTITUTE(A10," ","*",LEN(A10)-LEN(SUBSTITUTE(A10," ","")))))

Thank you for any help.

 

[dreid1011]

Will it always be 3 numbers at the end of the string? Or could you have other text after the last number? And are the numbers always 5 characters long (including decimal) ?



For the example above if the format is exactly the same for all strings...

2nd to last number
=MID(A10,FIND("@",SUBSTITUTE(A10," ","@",(LEN(A10)-LEN(SUBSTITUTE(A10," ","")))-1))+1,5)

3rd to last number
=MID(A10,FIND("@",SUBSTITUTE(A10," ","@",(LEN(A10)-LEN(SUBSTITUTE(A10," ","")))-2))+1,5)

 

[caabyyc]

d10=TRIM(MID(SUBSTITUTE($A10," ",REPT(" ",500)),(SUMPRODUCT((MID($A10,ROW($1:$999),1)=" ")*1)-COLUMN(A1))*500,500)) copy to E10

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C	D
1	704938 1-6/9" Dia. 12L14 Metal Row Hard 1 5.007 4.410 4.4330	5.007	4.410	4.4330
Sheet3

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),600),200))
C1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),400),200))
D1	=RIGHT(A1,SEARCH(" ",A1)-1)

 

[DerekWooley]

Thank you very much. These all work great.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jtakw]

Hi Fluff,

Your D1 formula in Post # 4 might need some tweaking...

 

[Fluff]

You're quite right, not quite sure what I was thinking when I did that, but by pure fluke it worked on this occasion.
it should be
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))