Skip to content

Capture the Last Word in Excel or Sheets

Another common question for spreadsheet users is: how do I get only the last word from the text in a single cell?

Luckily, if you know which formula to use, accomplishing this task is very simple.

I will show you a method that works for Excel and a different method that works for Google Sheets.

How to Capture the Last Word in Excel

Let’s say you have a list of full names like the example shown below.

You would use the following formula to capture the last word, or last name in this case, of the person listed in cell A2.

=TEXTAFTER(TRIM(A2)," ",-1)

This formula first uses the TRIM function to remove any spaces before or after the words in the text, leaving only those between each word.

The TEXTAFTER function then searches for the delimiter specified in the second argument, which is the space. Furthermore, the negative one for the third argument forces the function to look for the first instance of the delimiter starting from the right of the text string.

Once the TEXTAFTER function locates the delimiter, it returns all of the text that comes after it. Therefore, we get back the last word of the text.

And once you have the first formula written, you can use the fill handle to drag down the formula for all the other cells containing text.

See It In Action

Check out the video below to see this demonstrated in Excel.

How to Capture the Last Word in Google Sheets

Let’s say you have a list of full names like the example shown below.

You would use the following formula to capture the last word, or last name in this case, of the person listed in cell A2.

=INDEX(SPLIT(A2," "),COUNTA(SPLIT(A2," ")))

The SPLIT function, which appears twice in this formula, separates the text string into individual words. The SPLIT function breaks up the string based on the selected delimiter, which in this case, is a single space.

Then, the INDEX function, coupled with the COUNTA function, returns the last word among the list of words. The COUNTA function returns a count of all the words, and the INDEX function uses that number to return the last word in the list.

And once you have the first formula written, you can use the fill handle to drag down the formula for all the other cells containing text.

See It In Action

Check out the video below to see this demonstrated in Google Sheets.

This Post Has One Comment

  1. Thanks for the two examples. It’s so interesting when you find a difference in Excel and Sheets. Great videos too!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top