Question on returning last word in text string

[leopardhawk]

I am wondering if it is possible for a formula to return only the last part of a text string (without the $) from another cell?

ie. in this example, the formula would return 81416.

"over $47,630 up to $81,416"

Hoping for a solution.

Thanks!

 

[Joe4]

Assuming that all entries look like that and have two "$" in them, try this for an entry in cell A1:

=SUBSTITUTE(MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,LEN(A1)),",","")

If you need to return a numeric value instead of text, try this variation instead:

=MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,LEN(A1))+0

 

[jtakw]

Hi,

Result converted to Real number:

Book1
	A	B
1	over $47,630 up to $81,416	81416
Sheet667

Cell Formulas
Range	Formula
B1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)+0

 

[DanteAmor]

Just another way:

=--REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"$","@",2)),"")

 

[leopardhawk]

Many thanks to all three of you who responded! I have tested all three solutions and they all work perfectly. I also appreciate your foresight that I would want the result converted to a real number!

Thank you so much... I also gain a better insight into some of the more subtle nuances of Excel. Always learning!

Cheers!

 

[DanteAmor]

Many thanks to all three of you who responded! I have tested all three solutions and they all work perfectly. I also appreciate your foresight that I would want the result converted to a real number!

Thank you so much... I also gain a better insight into some of the more subtle nuances of Excel. Always learning!

Cheers!

It is correct, in excel we are always learning.
Thank you for the feedback.

 

[jtakw]

You're welcome, thanks for the feedback.