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
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.
Part by part
- =RIGHT(
- Takes characters from the end of the text. The count is computed rather than fixed, since surnames vary in length.
- LEN({{full_name}})
- The total character count. Subtracting the position of the space gives exactly the number of characters after it.
- FIND(" ", {{full_name}})
- The position of the first space. FIND is case-sensitive, which is irrelevant for a space and matters if you adapt this for a letter.
Common mistakes
=RIGHT(A2, LEN(A2) - FIND(" ", A2) + 1)The plus one includes the space itself, so every result begins with a leading space. It is invisible on screen and breaks every subsequent comparison and lookup.
=RIGHT(A2, LEN(A2) - FIND(" ", A2))=RIGHT(A2, 10)A fixed count truncates long surnames and swallows part of the first name on short ones. The whole reason for the LEN and FIND arithmetic is that the answer is not a constant.
Applying it to a single-word cellFIND returns #VALUE! when there is no space, so a mononym or a blank cell breaks the formula. Wrap it in IFERROR returning the original cell if your data is not guaranteed to have two parts.
Questions
What happens with a middle name?
It returns everything after the first space, so "Ada Byron Lovelace" yields "Byron Lovelace". To get the last word only, search from the right by substituting the final space for a marker character.
How do I get the first name instead?
LEFT with FIND minus one: =LEFT(A2, FIND(" ", A2) - 1). The minus one is what excludes the space, exactly mirroring why the plus one is wrong in this formula.
Is there something simpler now?
TEXTSPLIT in current Excel splits on a delimiter directly and is far clearer. Sheets has SPLIT. Both are recent enough that this formula remains the portable choice for shared workbooks.
What about names that do not split this way?
Many do not — compound surnames, particles like van and de, and cultures placing the family name first. Any automatic split is a guess, so keep the original column and treat the result as a convenience rather than data.