Text Needed to the right of text

[Silvermini63]

I have a cells full of text and I need to split out from it everything that is right of the 3rd to last space in the string.

The common in the strings is all the info I need starts after the 3rd space from the end of the text.
The issue is everything to the left of that 3rd to last space is a different length
eg:
product 1 010001 6 $10.00

 

[Robert Mika]

product 1 010001 6 $10.00</SPAN>	010001 6 $10.00</SPAN>
prodctac ak 1 01001 6 $20</SPAN>	01001 6 $20</SPAN>
kaka iom spp 2 01000 9 $30</SPAN>	01000 9 $30 </SPAN>

<TBODY>
</TBODY>

B1 copy down:
=TRIM(RIGHT(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3),LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3))-1))

 

[Rick Rothstein]

Assuming your text is never longer than 99 characters, then I am pretty sure this formula will return what you want...

=TRIM(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",99)),198))

If your text could be longer, then change the 99 to the maximum possible length and replace the 99 with double whatever that maximum possible length is.

 

[Silvermini63]

Both don’t work to how I need it to the first one seems to find the Number after the product in my example; product 1 it then will return what is after that but in real terms my products don’t have that number after it.

The second returns everything after the code so in my example it returns 6 $10.00 not 010001 6 $10.00

So my real products maybe
Rice long grain 010001 6 $10.00
Par baked bread 010022 42 $25.00

So I would need
010001 6 $10.00
010022 42 $25.00

Thanks so far for your help I think we are close

 

[Rick Rothstein]

Both don’t work to how I need it to the first one seems to find the Number after the product in my example; product 1 it then will return what is after that but in real terms my products don’t have that number after it.

The second returns everything after the code so in my example it returns 6 $10.00 not 010001 6 $10.00

Sorry, my fault. I thought I changed the last value in my formula before posting it so that it would grab the correct text, but I see that the value didn't update. No problem, this formula should do what you want (just had to add 99 to the value for what ended up getting posted)...

=TRIM(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",99)),297))

 

[Silvermini63]

Thank you so much for your kind help it works perfectly