How to extract the last word in a cell in Excel

For example, you have the following the text in a cell “A1”:

Can you help me extract the last word in this cell in Excel?

To extract the last word “Excel”, use one of the following formula:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

or

=MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,256)

The result:

Excel

This entry was posted in Extracting words. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

11 Comments

  1. Posted February 4, 2015 at 1:29 pm | Permalink

    Very helpful ! Thanks a lot ! 🙂

    • karen
      Posted February 4, 2015 at 1:30 pm | Permalink

      You’re welcome Chetan!

  2. vishnu
    Posted November 28, 2014 at 3:28 am | Permalink

    Very helpful!!I was searching this.

    I would like to know how to insert a blank space after the last word? Please kindly help me.

    • karen
      Posted January 22, 2015 at 5:00 pm | Permalink

      Hi Vishnu! That’s easy just add &" " to the end of your formula.

  3. Mahesh
    Posted July 4, 2014 at 8:56 am | Permalink

    It was very useful. Thanks a ton ☺

    • karen
      Posted August 8, 2014 at 9:53 am | Permalink

      You’re welcome, Mahesh!

  4. Sarah
    Posted June 26, 2014 at 9:56 pm | Permalink

    Thank you for this info. It helped me heaps!
    ( So much easier than extracting each one individually )

  5. Posted June 15, 2014 at 12:36 pm | Permalink

    Inexplicably the last word formula fails for data Joakina (Joakim?) Enoksen. In Excel 2010 on my English Windows 8.1 laptop in Norway.

    • karen
      Posted February 4, 2015 at 1:32 pm | Permalink

      Hi Alf! I just tried it and it works. Using any of the two formulas above, I am able to extract “Enoksen” from the string “Joakina (Joakim?) Enoksen”. Can you add a link to the Excel file? I can try to de-bug it for you!

  6. Bilal
    Posted February 12, 2014 at 11:18 pm | Permalink

    Karen–

    You are AWESOME!

    • karen
      Posted March 12, 2014 at 1:35 pm | Permalink

      You are very welcome, Bilal! So happy I was able to help 🙂

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*