How to remove the last word in a cell in Excel

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

I want to remove the last word in a cell in this cell

To remove the last word in cell “A1″, use one of the following formula:

=IF(ISNUMBER(SEARCH(" ",TRIM(A1))),TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"")),A1)

The result:

I want to remove the last word in a cell in this

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

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

6 Comments

  1. Danny
    Posted October 7, 2014 at 4:12 am | Permalink

    Hi Karen, the code works, but is there any way to get the second to last word? And also, a way to ignore the last two words?

    • karen
      Posted November 5, 2014 at 2:07 pm | Permalink

      Hi Danny, do you want to remove the second to the last word? Or do you want to remove the last 2 words?

  2. Sam
    Posted December 8, 2013 at 7:55 pm | Permalink

    Awesome, worked a treat. Copy, Paste, Done!

    • karen
      Posted December 9, 2013 at 12:17 pm | Permalink

      Glad it worked for you, Sam! :)

  3. Posted June 18, 2013 at 3:24 am | Permalink

    some cells is removing letters from 1st word aswell

    • karen
      Posted June 20, 2013 at 10:52 am | Permalink

      Hi Lenin, I understand that the first word of the cell is being removed too. Can you send me a link to the Excel sheet that you’re working on, so that I can see what’s wrong? I just tried the formula above and it is working for me. I am sorry to hear that it is not working properly for you.

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=""> <strike> <strong>