How to Extract Last Word from a Cell in Excel (Formula)

puneet-gogia-excel-champs

- Written by Puneet

To get the last word from a cell, you must combine the TRIM, RIGHT, LEN, REPT, and SUBSTITUTE functions in Excel.

These functions help you locate the last space from the cell, and then, based on that space; you can get the last word extract. Excel doesn’t have an exact formula to complete it, so you need to create a custom formula.

extract-last-word-from-a-cell

Excel Formula to Get the Last Word from a Date

Here’s the formula that you need to use:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

To understand how this formula works, we need to break this formula into parts.

In this first part, we have REPT and LEN functions. The LEN function returns the count of characters in the original value, and the REPT function returns spaces according to that number (character count).

rept-and-len-functions

If there are 59 characters in the original value, REPT will return 59 spaces. See the below screenshot.

rept-returns-values

Next, in the second part, we have the SUBSTITUTE function, which replaces each space in the cell with the spaces returned by the REPT function.

substitue-replace-each-space

If there are 10 spaces in the cell, the value returned by the SUBSTITUTE returns 10×59 spaces. 59 spaces between each word instead of 1. See the screenshot below.

value-returned-by-substitute

After that, in the third part, we have the RIGHT function that extracts characters (equal to the character count of the original value) from the value we have got above.

right-function-extracts-characters

And below is the value it returns.

trim-value-in-return

As there are 59 characters in the original value, it extracts 59 characters from the new values. In this extracted value, we have 6 characters of the last word (guide), and the rest are the spaces.

Now in the fourth part, we have the TRIM function that removes the extra spaces from the value and returns only the last word (guide).

trim replaces extra spaces

Get the Excel File

Last Updated: December 02, 2023