Text & Cleanup

Excel formula for Extract the last word from text

Pull the last word (e.g. a surname) from a cell of any length.

Copy this formula

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

How the formula works

SUBSTITUTE pads each space to 100 spaces, RIGHT then grabs the final 100 characters (the last word plus padding), and TRIM cleans it up. It works no matter how many words the cell has.

Examples

=TRIM(RIGHT(SUBSTITUTE("Mary Jane Watson", " ", REPT(" ",100)), 100))→Watson

Returns the last word.

Other ways to do this

365 alternative

=TEXTAFTER(A2, " ", -1)

TEXTAFTER with -1 grabs text after the last space.

FAQ

Why the REPT trick?

It pushes the last word to the far right so a fixed RIGHT() reliably captures it regardless of word count.

Related formulas

Split text into separate columns Find the position of a character

Functions used

TRIM RIGHT SUBSTITUTE REPT

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free