» Extracting last Word
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Creating a formula to retrieve the last word from a string
Solution:
Using the MID, FIND, SUBSTITUTE and LEN text category functions, as follows:
=MID(A2,FIND(""$"",SUBSTITUTE(A2,"" "",""$"",LEN(A2)-LEN(SUBSTITUTE(A2,"" "",""""))))+1,255)
Example:
String________________Last Word
Microsoft Excel_______Excel
abc defgh ijk_________ijk
last word only________only
Book Store:
Right Function
RNDL
I learn something from every one of these tips. Thanks.
For this one, though, I'd use
'=RIGHT(A3,LEN(A3)-FIND("$",SUBSTITUTE(A3," ","$",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))) instead. The "Right" function is more intuitive to me than the "mid" for what you are doing, and it doesn't include the arbitrary "255".
Reply: Vasanth Wagle
Alan
Hi Vasanth,
[QUOTE=Vasanth Wagle]Dear Sir/Madam,
The elp on commands which you are giving is very brief. Pls elaborate the same to give some more information. So that we can effectively use the same.
Regards
Vasanth[/QUOTE]Is there something in particular that you need help with?
If so, post back and someone should be able to assist.
Alan.
Excel Function
Mohamed Wajeeh
Translate a mathematical value or figure to English words
for example
A1=100.50
A2=One Hundred & cents Five Zero Only
Reply: Mohamed Wajeeh
Alan
Hi Mohamed,
[QUOTE=Mohamed Wajeeh]Translate a mathematical value or figure to English words
for example
A1=100.50
A2=One Hundred & cents Five Zero Only[/QUOTE]Google it!
[url]http://www.google.co.nz/search?hl=en&q=How+to+convert+a+numeric+value+into+English+words+in+Excel&btnG=Search&meta=[/url]
Alan
The elp on commands which you are giving is very brief. Pls elaborate the same to give some more information. So that we can effectively use the same.
Regards
Vasanth