How to Remove Only the Last Name Using an Excel Formula
Question details
The user needs to extract or delete the final word from a full name string, keeping all first and middle names intact, regardless of how many words the name contains.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning or formatting a large list of names where the user needs a dynamic way to separate or drop the last name without manually editing each cell.
- Observed behavior
- The user requires a specific formula capable of dynamically handling names with two, three, or more words.
Ensure your name data is formatted consistently with single spaces between words. You may want to use the TRIM function beforehand to clear out any accidental trailing or leading spaces that could interfere with the formula.
Use a Combination of TEXTJOIN, DROP, and TEXTSPLIT
This is the most efficient method for newer versions of Excel. It uses dynamic array functions to split the text, drop the last word, and stitch the remaining words back together.
The TEXTSPLIT function divides the full name into an array of words based on the spaces. The DROP function with a '-1' argument removes the very last item in that array. Finally, TEXTJOIN rebuilds the remaining words with spaces, and IFERROR ensures that single-word names remain unchanged.
Click on an empty cell next to your data (for example, B2) where you want the modified name to appear.
Type the following formula into the formula bar: =IFERROR(TEXTJOIN(" ",TRUE,DROP(TEXTSPLIT(A2," "),,-1)),A2)
Press the Enter key. The formula will extract the name while dropping the last word.
Click and hold the small green square (fill handle) at the bottom-right corner of cell B2, then drag it down to apply this formula to the rest of the names in your list.
Use WPS Spreadsheet to Manage and Edit Names Easily
WPS Spreadsheet fully supports advanced array functions like TEXTJOIN and IFERROR, making complex text manipulation tasks effortless. It is a highly compatible and lightweight alternative for your data processing needs.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your list of names.
- 2. Input the array formula: Select a blank cell adjacent to your data and enter =IFERROR(TEXTJOIN(" ",TRUE,DROP(TEXTSPLIT(A2," "),,-1)),A2).
- 3. Apply to all rows: Double-click the fill handle in the bottom-right corner of the cell to instantly apply the formula to the entire column.
Frequently Asked Questions
Can I use this formula in older versions of Excel?
Functions like TEXTSPLIT and DROP are only available in Microsoft 365 and Excel for the web. For older versions (like Excel 2016 or 2019), you will need a more complex combination of LEFT, FIND, LEN, and SUBSTITUTE functions to extract names.
How do I remove the first name instead of the last name?
To remove the first name and keep the rest, change the DROP function's argument from -1 to 1. The modified formula would be: =IFERROR(TEXTJOIN(" ",TRUE,DROP(TEXTSPLIT(A2," "),,1)),A2).
What if my names are separated by commas instead of spaces?
If your names are separated by commas, you need to change the delimiter in the TEXTSPLIT function. Replace the space " " with a comma "," like this: TEXTSPLIT(A2,","). You can also adjust the TEXTJOIN delimiter if you want the final output separated by commas.
