Skip to content

Extract the last name

Get the last word of a full name, even when there is a middle name.

The formula

Argument separator
Excel and Google Sheets
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))

How it works

  1. SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100))

    Replaces each space with 100 spaces, pushing the words far apart.

  2. RIGHT(…, 100)

    Takes the last 100 characters, which contain only the last word plus padding.

  3. TRIM(…)

    Removes the padding spaces.

Example

A: Full nameB: Result
Maria LopezLopez
John Paul SmithSmith
CherCher

Tips

  • In Excel 365: =TEXTAFTER(A2, " ", -1, , , A2) does the same.