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.
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).
If there are 59 characters in the original value, REPT will return 59 spaces. See the below screenshot.
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.
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.
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.
And below is the value it returns.
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).
