Reverse Find Assistance

[rex759]

Hello,
I feel like I am close but can't put it all together. I want to extract text positioned from the 3rd space from the right to the next space.

Example,
N/A 1000qz0000 Az00-Wztt Remote 9-op/ 1,129.00 0 ____ would produce 1,129.00.

I broke down the sections to get the position of the spacing and thought I could combine them but that didn't work.

Find characters in a string.xlsx
	A	B	C	D	E
1	MID	Beginning	Ending	Substitute	Num of Char
2	1,129.00	5	39	N/A 1000qz0000 Az00-Wztt Remote 9-op/*1,129.00 0 ____	34
3
4	N/A 1000qz0000 Az00-Wztt Remote 9-op/ 1,129.00 0 ____
Sheet4

Cell Formulas
Range	Formula
A2	A2	=MID(A4,39,8)
B2	B2	=LEN(A4)-LEN(SUBSTITUTE(A4," ",""))-2
C2	C2	=FIND("*",D2,1)+1
D2	D2	=SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))-2)
E2	E2	=C2-B2

Any help is appreciated.

 

[jtakw]

Hi,

B1 formula results Text
C1 formula converts result to Real number for further math:

Book3.xlsx
	A	B	C
1	N/A 1000qz0000 Az00-Wztt Remote 9-op/ 1,129.00 0 ____	1,129.00	1129
Sheet1026

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),100))
C1	C1	=LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),100)+0

 

[rex759]

Wow, I have to read up more on the "REPT" function. You solution looks much cleaner than the route I was taking. Works perfectly. Thank you.

 

[jtakw]

You're welcome, thanks for the feedback.