FormulaText
Pull the last name out of a full-name cell
Split "First Last" into a last-name column with one formula that runs in every version of Excel and in Google Sheets, no add-in needed.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
=RIGHT(A2, LEN(A2) - FIND(" ", A2))
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →FIND locates the position of the first space in the name; subtracting that position from the total length (LEN) gives exactly how many characters remain after the space — the last name's length.
- →RIGHT then grabs that many characters from the end of the string. Three text functions, none of them version-gated, is what makes this run on a decade-old workbook as well as a brand-new one.
- →The mirror version for a first name is simpler and needs no subtraction from LEN: LEFT stops right before that same space instead of counting backward from the end.
The fine print
- →Assumes exactly one space between first and last name — a middle name ("Jane Q Public") ends up prepended onto the result instead of dropped, since FIND stops at the FIRST space it finds.
- →A single-word cell with no space at all makes FIND fail with #VALUE!, since there's no space to locate — worth an IFERROR wrap on messy name lists.
- →Google Sheets' SPLIT and Excel 365's TEXTSPLIT do the same split in one step and handle extra spaces more gracefully, but TEXTSPLIT isn't available before Excel 2021/365 and SPLIT doesn't exist in Excel at all — this RIGHT/LEN/FIND version is the one that works everywhere.