The simplest Excel formula to extract a last name from a full name is =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)), assuming the name is in cell A1. This works for most names with one or more spaces, returning everything after the final space. For a cleaner version that handles trailing spaces, use =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)).
Why the Formula Works
The formula leverages a common Excel trick: replacing every space with many spaces, then taking the rightmost chunk. Here is the breakdown:
SUBSTITUTE(A1," ",REPT(" ",99))– replaces each single space with 99 spaces, creating huge gaps between words.RIGHT(...,99)– grabs the last 99 characters, which will contain the final word plus a large block of spaces.TRIM(...)– removes all extra spaces, leaving only the last word.
For example, with John Paul Smith in A1, the formula returns Smith. With Mary Jane Watson, it returns Watson. The number 99 works because no realistic name has a word longer than 99 characters; you can use 50 or 200, but 99 is safe.
When to Use a Simpler Formula
If every name in your list has exactly two words (first and last, no middle names), you can use a simpler approach: =TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1))). This finds the first space and returns everything after it. However, it fails if there is a middle name or extra spaces. The SUBSTITUTE/REPT method is more robust and is the recommended default.
For names with suffixes like John Smith Jr., the formula returns Jr., which may or may not be what you want. If you need to exclude suffixes, you would need a more complex formula or a helper column.
How to Handle Names with No Spaces
If a cell contains only a single word (e.g., Madonna), the formula returns that same word, which is correct if you consider the entire string as the last name. If you want to return an empty string or an error for single-word names, wrap it with an IF: =IF(ISNUMBER(SEARCH(" ",TRIM(A1))),TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),""). This checks if there is a space; if not, it returns blank.
Common Problems and Fixes
- Trailing spaces in the original name: Use the TRIM inside the formula as shown above, or clean the data first with
=TRIM(A1). - Multiple consecutive spaces: The SUBSTITUTE method treats each space individually, so
John Smith(two spaces) still yieldsSmithcorrectly. The simpler FIND method would fail. - Names with hyphens or apostrophes: These are fine; the formula only splits on spaces.
- Names with tabs or non-breaking spaces: Those are not regular spaces, so the formula won’t split. Use
=TRIM(CLEAN(A1))first to remove non-printable characters. - All-caps or mixed case: The formula preserves case. Use
=PROPER(...)if you need title case.
Alternative: Using Flash Fill
If you prefer not to use a formula, Excel’s Flash Fill (introduced in 2013) can extract last names without any formula. Type the expected last name in the adjacent column, press Ctrl+E (Windows) or Cmd+E (Mac), and Excel will fill the rest based on your pattern. This is quick for one-off tasks, but it is not dynamic – if the source data changes, the extracted names do not update. For a live formula that recalculates, use the SUBSTITUTE/REPT method.
Extracting First and Middle Names
Once you have the last name, you might also want the first name. A common companion formula for the first name is =TRIM(LEFT(A1,FIND(" ",A1)-1)) – this returns everything before the first space. For the middle name(s), you can use =TRIM(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)) but that only works for exactly one middle name. A more robust way is to use =TRIM(SUBSTITUTE(SUBSTITUTE(A1,first_name,""),last_name,"")) after computing first and last, but that can be messy. For most users, the last-name formula is the priority, and first names can be extracted with the simple LEFT formula.
Frequently Asked Questions
Q: Does this formula work in Google Sheets?
Yes, the same formula works in Google Sheets because SUBSTITUTE, RIGHT, and TRIM are standard functions there. The only difference is that you might need to use semicolons as argument separators if your locale uses them, but the syntax is otherwise identical.
Q: What if the name has a comma like “Smith, John”?
That formula would return “, John” because it splits on spaces, not commas. For comma-separated names, use =TRIM(LEFT(A1,FIND(",",A1)-1)) to get the last name before the comma, or =TRIM(MID(A1,FIND(",",A1)+1,99)) for the first name after the comma.
Q: How do I handle names with three or more spaces?
The SUBSTITUTE/REPT method handles any number of spaces because it always takes the last chunk after the final space. For example, Ann Marie Van Der Berg returns Berg, which is correct if you consider the last word as the surname.
Q: Can I use a shorter formula?
Yes, if you have Excel 365 or Excel 2021, you can use =TEXTAFTER(A1," ",-1) which directly returns the text after the last space. This is cleaner and easier to read, but it requires a newer version. For older Excel, the SUBSTITUTE/REPT trick is the standard workaround.
Q: Why does my formula return a #VALUE! error?
This usually happens if the cell contains a number or a date instead of text. Ensure the cell is formatted as text, or use =TEXT(A1,"@") to force text. Also, check for non-breaking spaces (character 160) which are not recognized by SUBSTITUTE; use =SUBSTITUTE(A1,CHAR(160)," ") first.
Q: Is there a way to do this without a formula, like with Power Query?
Yes, Power Query (Get & Transform Data) has a “Split Column by Delimiter” feature that can split names into first and last name columns. Go to Data > From Table/Range, then select the column and use Home > Split Column > By Delimiter, choosing Space and “Rightmost delimiter”. This is useful for large datasets, but it is not a live formula – you must refresh the query when data changes.
Related
- Excel Conditional Formatting Color Scales Explained
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
- Excel Center Louisville KY: What It Is & How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Unable to Put a Filter in Excel? Common FixesIf you are unable to put a filter in Excel, select a cell in your data and choose…
- Array Formula in Excel: Examples, Uses, and Key TipsAn Excel array formula works with multiple values at once, either returning one result or spilling a set…
- Calculating NPV and IRR in Excel: Formulas and StepsTo calculate NPV in Excel, discount the future cash flows with NPV and add the initial cash flow…
- X++ String Runtime Functions: How They Work and When to Use ThemX++ string runtime functions are built-in methods that let you manipulate, compare, and inspect text values at runtime…
- Oracle SQL String Aggregate Functions: LISTAGG GuideOracle SQL’s main string aggregate function is LISTAGG: it combines values from multiple rows into one delimited string,…
- Excel IFS Function With Dates: Examples and Date RulesYes. Excel’s IFS function can test dates just like other values: compare a date cell with TODAY(), another…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.