Excel Formula to Remove Middle Names and Return First Last
Question details
The user needs to convert a list of names formatted as "Last, First Middle" into a "First Last" format by stripping out the middle name or initial using a formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and standardizing a dataset of names for reporting, mailings, or database entry.
- Observed behavior
- Names are currently displayed as "Last, First Middle" (e.g., Smith, John J) and must be reformatted to "First Last" (e.g., John Smith), accounting for entries that may not have a middle name.
Ensure your dataset consistently uses a comma to separate the last name from the first name, as the formula relies on this punctuation to accurately split the text.
Use a Combination of MID, FIND, and LEFT Functions
Apply a composite formula that locates the comma separator, extracts the first name up to the next space, and appends the last name.
This approach uses the FIND function to locate the comma and the space, which identifies where the first name begins and ends. The MID function then extracts the first name, and the LEFT function grabs the last name. The IFERROR function handles cases where a middle name is missing, preventing formula errors.
Click on the empty cell where you want the reformatted "First Last" name to appear.
Assuming the original name is in cell G4, type the following formula: =MID(G4,FIND(",",G4)+2,IFERROR(FIND(" ",G4,FIND(",",G4)+2),100)-FIND(",",G4)-2)&" "&LEFT(G4,FIND(",",G4)-1)
Press Enter to execute the formula. Check the cell to ensure it successfully displays the name in the "First Last" format, like "John Smith".
Hover over the bottom-right corner of the cell until the cursor turns into a plus sign (Fill Handle). Click and drag downwards to apply the formula to the remaining rows in your dataset.
Clean and Organize Data Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports complex text-handling formulas, allowing you to reformat large lists of names effortlessly. With a familiar interface, it streamlines your data management tasks without slowing down.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the file containing your name data.
- 2. Paste the Formula: Select the target cell next to your data and paste the provided combination formula.
- 3. Fill the Column: Use the intuitive fill handle to drag the formula down to automatically reformat thousands of names instantly.
- 4. Save and Share: Save your neatly formatted document in .xlsx format, ensuring flawless compatibility when sharing with Microsoft Excel users.
Frequently Asked Questions
Can I remove middle names without using a complex formula?
Yes, you can use the 'Text to Columns' feature located in the Data tab. Use commas and spaces as delimiters to separate the names into different columns, then simply delete the middle name column and concatenate the remaining columns.
Why does the formula return a #VALUE! error?
The #VALUE! error usually occurs if the referenced cell does not contain a comma or if there are unexpected leading spaces. Ensure every cell in your source column strictly follows the "Last, First" format.
Does this formula work if there is a prefix or suffix in the name?
This specific formula is strictly designed for "Last, First Middle" structures. If your data includes suffixes (like Jr. or Sr.) or prefixes (like Dr.), they will likely be extracted incorrectly, and you may need a more advanced cleaning tool like Power Query.
