Spreadsheet Practice

How to extract the last name from a full name in Excel

Excel formula

=TEXTAFTER(A2," ",-1)

In Excel 365 use =TEXTAFTER(A2," ",-1), which returns everything after the last space. In older versions use =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).

Result on the example
one result per row in B2, filled down to B8
Works in
Microsoft 365 only
Google Sheets
Different formula, see below

See it work

The formula is already in B2, filled down to B8. Change any input and the result updates, or edit the formula to experiment.

How it works

The third argument of TEXTAFTER is the instance number, and a negative number counts from the end. -1 means the last space, so middle names and initials are skipped and only the final word comes back.

In Google Sheets, [^ ]+$ matches the run of non-space characters at the end of the text.

=TEXTAFTER(A2," ",-1)

Result"Lopez"

  1. A2"Maria Lopez"

    The value in A2.

  2. -1-1

    Makes the value negative.

  3. TEXTAFTER(A2," ",-1)"Lopez"

    TEXTAFTER: Text after a delimiter.

    • textA2"Maria Lopez"
    • delimiter" "
    • instance_num-1-1

In Google Sheets

Google Sheets needs a different formula: =REGEXEXTRACT(A2,"[^ ]+$"). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))Lopez, O'Neil, Smith…Works in every version. Each space becomes 100 spaces, so the last 100 characters hold only the last word plus padding, which TRIM removes.
=INDEX(SPLIT(A2," "),1,COUNTA(SPLIT(A2," ")))Google SheetsLopez, O'Neil, Smith…Google Sheets: split the name into words and take the last one.

Common mistakes

  • A trailing space makes the "last word" empty. Clean the column first: =TEXTAFTER(TRIM(A2)," ",-1).
  • Suffixes come back as the last name: "Robert Kim Jr." returns "Jr.". Remove them with SUBSTITUTE before extracting.
  • A single-word name returns #N/A. Give TEXTAFTER a fallback: =TEXTAFTER(A2," ",-1,,,A2).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Extract the last name from a full name

In B2, extract the last name (everything after the last space) and fill down to B8.

Questions

How do I get the last name when the cell says "Lopez, Maria"?

Take what comes before the comma: =TEXTBEFORE(A2,",").

How do I get the last word of any sentence?

The same formula works on any text: =TEXTAFTER(A2," ",-1) returns the last word.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.