Extract substring after last space in text

[fklioside]

I am trying to extract the last substring in some text that I have. Please see below for example.

12/30-12/31 Bob Dylan 40.25

I need to extract the 40.25 out. Every line item may be different but the number I am extracting is always after the last space. I tried using mid and find but this doesn't work well when there is more than 1 space in the text.

Thank you in advance!

 

[Son]

Supposing that the string is in cell A1, try this:

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))

The " " in the formula searches for the space character.

 

[Rick Rothstein]

Here is another formula for you to try...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))

 

[fklioside]

Great that works! This formula has so many variables. I am trying to follow and understand it but not sure why or whne you would use these ><

 

[hiker95]

fklioside,

And, another variation:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))