Oct 9 2009

How Do I Get Just the Last Word from a Cell?

This is an example of a question I recently received in the comment section of a post that I think others will find useful.

[ad#in-post-ad]

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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

13 Comments on this post

Trackbacks

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. Adam Clark said:

    Thanks for the help Phil! That worked!

    October 29th, 2009 at 11:41 am
  7. 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
  8. john said:

    Good suggestion Rick. Thanks.

    December 13th, 2009 at 2:00 am
  9. 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
  10. Allison said:

    I have a spreadsheet that has several pieces of data entered into a single cell, separated by lines. Is formula i can enter to see how often each piece of information appears first on the line?

    April 11th, 2011 at 2:46 pm
  11. carrel said:

    This is a great formula! Thanks a lot!

    July 21st, 2011 at 8:30 pm
  12. Ed said:

    Thank you Phil. It works fine after changing the quotation marks in excel.

    March 30th, 2013 at 5:12 pm
  13. Sandy said:

    Hi,

    I have file contacts.CSV which has 35000 contacts and i want to separate last word from one column to another column. please help as soon as possible pleaseeeee….

    May 14th, 2013 at 6:12 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives