How to Extract a Multi-Word Last Name from a Full Name in WPS Office Spreadsheets
Question details
The user needs a formula to extract all words after the first name from a full name and remove an optional nickname in parentheses.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Full names such as “Mickey De La Ratones (nickname)” are stored in cells, and the user wants only the multi-word last name as the result.
- Observed behavior
- The expected result for “Mickey De La Ratones (nickname)” is “De La Ratones”, without the first name and without the parenthetical nickname.
Confirm that your WPS Office Spreadsheets version supports the LET and TEXTAFTER functions, or be ready to use the MID, FIND, and TRIM alternative. Place the full names in a consistent format in a known column such as column A.
Use LET and TEXTAFTER to Extract the Last Name and Remove a Nickname
This is the shortest and most readable formula for current WPS Office versions and Microsoft 365-compatible workbooks.
The formula removes the first name by taking everything after the first space, then stops at the opening parenthesis if a nickname exists. TRIM removes any extra leading or trailing spaces.
Open the WPS Office Spreadsheets application and load the workbook that contains the full names.
Click the cell where you want the result to appear, such as B1.
Type =LET(rest,TEXTAFTER(TRIM(A1)," "),p,IFERROR(FIND("(",rest),LEN(rest)+1),TRIM(LEFT(rest,p-1))) and press Enter.
If the remaining names are in A2, A3, and so on, drag the fill handle from B1 down the column or copy the formula to the other output cells.
Use MID, FIND, and TRIM for Versions Without TEXTAFTER
Use this alternative when TEXTAFTER or LET is not available, or when the recommended formula returns a #NAME? error.
Extract Multi-Word Last Names in WPS Office Spreadsheets
WPS Office Spreadsheets can directly perform this Excel-compatible name cleanup task. Enter the LET and TEXTAFTER formula to return the multi-word last name while dropping an optional parenthetical nickname.
- 1. Download and open WPS Office: Download WPS Office from the official website, install it, and open WPS Office Spreadsheets.
- 2. Open the Excel workbook: Open the .xlsx workbook that contains full names, or enter the full names in column A.
- 3. Enter the extraction formula: In B1, enter =LET(rest,TEXTAFTER(TRIM(A1)," "),p,IFERROR(FIND("(",rest),LEN(rest)+1),TRIM(LEFT(rest,p-1))) and press Enter.
- 4. Fill the formula down: Drag the fill handle from B1 down to apply the formula to the rest of the name list.
- 5. Save in Excel format: Save the completed workbook as .xlsx to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
What does the formula return when a name has no nickname in parentheses?
The IFERROR part returns the full remaining text after the first name. TRIM then removes extra spaces, so “Mickey De La Ratones” returns “De La Ratones”.
Can this formula extract multiple last name words like “De La Ratones”?
Yes. TEXTAFTER(TRIM(A1)," ") takes everything after the first space, so it keeps all remaining words before the optional opening parenthesis.
Does this work in every version of WPS Office?
The LET and TEXTAFTER formula needs a WPS Office version that supports these dynamic array functions. If you see a #NAME? error, use the MID, FIND, and TRIM alternative provided above.
How do I apply the formula to hundreds of names at once?
Enter the formula in the first result cell, then double-click the fill handle or drag it down the column to copy the formula to all adjacent rows.
What happens if the nickname appears somewhere else in the cell?
The formula stops at the first opening parenthesis after the first name. If the data uses a different nickname style, adjust the FIND character or place nicknames in a separate column.
