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.