Excel Formula to Extract Last Name from Full Name

Splitting names in Excel is a common data cleaning task. Here are formulas for the most common name formats.

Format: "First Last" — Extract Last Name

=RIGHT(A2, LEN(A2)-FIND(" ",A2))

This finds the space, then extracts everything to the right of it.

Format: "First Middle Last" — Extract Last Name Only

=RIGHT(A2, LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

This replaces the last space with a marker (*) and extracts everything after it. Works for any number of name parts.

Format: "Last, First" — Extract Last Name

=LEFT(A2, FIND(",",A2)-1)

Extract First Name Too

=LEFT(A2, FIND(" ",A2)-1)

Excel 365: Use TEXTSPLIT (Easiest Method)

=TEXTSPLIT(A2," ")

This splits the name into separate cells by space. For the last element, combine with INDEX:

=INDEX(TEXTSPLIT(A2," "),COUNTA(TEXTSPLIT(A2," ")))

Flash Fill (No Formula Needed)

Type the last name of the first entry in an adjacent column, then press Ctrl+E. Excel will auto-fill the pattern.

FAQ

How do I extract the last name from a full name in Excel?

=RIGHT(A2, LEN(A2)-FIND(" ",A2)) works for "First Last" format.

How do I split first and last name into separate columns?

Use Data → Text to Columns → Delimited → Space. Or use Flash Fill (Ctrl+E).

What if the name has a middle name?

Use the SUBSTITUTE formula above that finds the last space, or use TEXTSPLIT in Excel 365.

Related: Capitalize First Letter in Excel and Extract Text Between Characters.