How Do I Get Just the Last Word from a Cell?
- 9 Comment
This is an example of a question I recently received in the comment section of a post that I think others will find useful.
So the following question was asked in a post’s comment section:
For example, we have the string “Let’s play a game”.
Is there a fomula out there that would be able to tell me that the last work in that string is “game”? HOW! Please share because this question is driving me nuts!
It was interesting, because just the other day another reader had posted a suggestion on a solution this exact problem. After a slight modification to the formula, we came up with the following formula to solve this issue.
So to get the last word from a string of words in a cell separated by spaces, try this formula:
=IF(ISERROR(FIND(” “,A1)),A1,TRIM(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”")))))))
Random Posts
9 Comments on this post
Trackbacks
-
Adam Clark said:
I tried using this formula and it returned the entire string of words. What am I doing wrong?
October 15th, 2009 at 6:52 pm -
john said:
It should select the last word of strings that are separated by spaces. Did you just copy and paste the formula into the spreadsheet?
October 15th, 2009 at 10:24 pm -
Automobile India said:
If you want to know the first 1000 square numbers, what formula would you type in Exel?
October 22nd, 2009 at 5:43 am -
Phil said:
If you just copy and paste this you will need to replace the quotation marks. The ones above which appear like ” are a Microsoft Word format and will need to be typed over in Excel. you’ll notice the ” changes to ”
The formula will then work.
October 28th, 2009 at 9:08 am -
Phil said:
ok the comment box formats the ” as Microsoft Word Format.
Excel format is a straight version whereas the Word format appears italic. Excel doesnt recognise the italic Word format.
October 28th, 2009 at 9:09 am -
Adam Clark said:
Thanks for the help Phil! That worked!
October 29th, 2009 at 11:41 am -
Rick Rothstein (MVP - Excel) said:
Here is a shorter formula to do this…
=TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,99)),99))
December 13th, 2009 at 1:21 am -
john said:
Good suggestion Rick. Thanks.
December 13th, 2009 at 2:00 am -
Automobile India said:
How do I import info from a table cell in Word into a table cell in excell?
February 1st, 2010 at 11:32 am
Loading ...