How to Extract Last Names from Two-Part or Three-Part Names in Excel
Question details
The user needs to extract the final word from a list of full names that may include either just a first and last name, or a first, middle, and last name.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing or cleaning up a spreadsheet where full names are combined in a single cell, and only the last names are needed in a separate column.
- Observed behavior
- Currently, full names are located in one cell, and the goal is to isolate and extract the text string appearing after the final space character in each cell.
Ensure your column containing the full names has no invisible trailing spaces at the end of the text, as this can cause text-extraction formulas to return blank or incorrect results.
Use the TEXTAFTER Function (Microsoft 365)
For users with Microsoft 365, the TEXTAFTER function is the quickest and most modern way to extract the last word in a cell based on a delimiter.
Click on the empty cell where you want the extracted last name to appear, such as cell B2.
Type =TEXTAFTER(A2," ",-1) into the formula bar. Replace 'A2' with the actual cell reference containing the full name.
Press Enter to see the extracted last name. Click and drag the fill handle (the small square at the bottom right of the cell) down the column to apply the formula to the rest of the names.
Use the RIGHT, SUBSTITUTE, and REPT Functions (Older Versions)
If you are using Excel 2019 or older versions where the TEXTAFTER function is not available, you can combine classic text functions to achieve the same result.
Easily Extract Text and Manage Data with WPS Office
WPS Spreadsheet fully supports advanced text functions and classic nested formulas to help you extract last names and clean your data efficiently. It is a highly compatible, free alternative to Microsoft Excel with a familiar interface.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the lists of names.
- 2. Apply the extraction formula: Click the target cell and enter the extraction formula, such as =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).
- 3. Fill down the column: Hit Enter to see the last name, then double-click the fill handle to automatically extract the last names for the entire list.
Frequently Asked Questions
What if the name has a suffix like 'Jr.' or 'Sr.'?
If a suffix is present at the end of the name, these formulas will extract the suffix instead of the last name (e.g., returning 'Jr.'). You may need to clean the data first by using Find and Replace to remove suffixes before applying the formula.
Why is the formula returning a blank result?
This usually happens if there is an accidental trailing space at the very end of the name in the original cell. To fix this, you can wrap the original cell reference in a TRIM function, such as using TRIM(A2) inside your extraction formula, to remove invisible trailing spaces.
Can I use Flash Fill to extract last names instead of formulas?
Yes, Flash Fill is a great alternative if you prefer not to use formulas. Type the last name manually in the adjacent cell next to the first name, press Enter, and then press the Ctrl + E shortcut. Excel will recognize your pattern and fill in the rest of the column automatically.
