Excel functions / Text
Extract the last word
Pull out the last word of some text, such as a surname, however many words come first.
How it works
In Microsoft 365, TEXTAFTER with an instance number of -1 splits at the last space: =TEXTAFTER(A2," ",-1). It works for "Nina Shah" and "Mary Ann Lee" alike.
In older versions there is a well-known trick: replace every space with lots of spaces, take a big slice from the right, and trim it: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | |||
| 2 | Nina Shah | nina.shah@example.com | ||
| 3 | Omar Reid | omar@example.com | ||
| 4 | Mary Ann Lee | mary.lee@example.com | Lee | |
| 5 |
Result: Lee. Mary Ann Lee has two spaces. -1 splits at the last one.
Tips
- Trailing spaces would make the last word empty, so clean the data with TRIM first.
Practise Extract the last word
Try it yourself in a real spreadsheet, with instant feedback on every answer.