I have a spreadsheet with full names in a single column cell in my Excel spreadsheet, but I need to split them into separate columns for first and last names. How can I simplysplit these names without manually typing them out – specially those names comprising multiple word family names?
Sample Dataset
Here’s a sample dataset in CSV format which we will use in this short tutorial.
Full Name
John Doe
Jane Smith
Robert Johnson
Emily White-Brown
Michael Lee
Sarah O'Connor
Thomas von Heidelberg
Importing the CSV File
To import the comma separated value file into Excel 365:
- Open a new Excel workbook.
- Go to the “Data” tab in the Excel ribbon.
- Click on “From Text/CSV” in the “Get & Transform Data” group.
- Browse and select the CSV file you saved.
- In the import wizard, ensure that “Comma” is selected as the delimiter.
- Click “Load” to import the data into your worksheet.
Now you have the sample data in your Excel worksheet, ready for name separation.
Split First and Last names to cells in Excel
Separating first and last names in Excel can be achieved using the Text to Columns feature. Here’s a step-by-step guide to accomplish this task:
- Open your Excel spreadsheet containing the full names.
- Select the column containing the full names (in our sample, it’s column A).
- Go to the “Data” tab in the Excel ribbon.
- Click on “Text to Columns” in the “Data Tools” group.
- In the Convert Text to Columns Wizard:
- Step 1: Choose “Delimited” and click “Next”.
- Step 2: Select “Space” as the delimiter and click “Next”.
- Step 3: Choose the data format for each column (usually “General” works fine) and click “Finish”.
Excel will now separate the names into two columns. The first column will contain the first names, and the second column will contain the last names.
Handling Multiple-Word Names
For names with middle names or multiple-word last names, you may need an additional step:
- After using Text to Columns, select the column with last names.
- Use the following formula in an adjacent empty column:
=TEXTJOIN(" ", TRUE, B2:D2)
(Assuming A2:C2 contains the split name parts)
- Fill down the formula for all rows by dragging the D2 cell bottom right corner.
This formula will combine all parts of the name except the first name into a single last name cell.
What to do if something goes wrong?
- If names are inconsistently formatted (e.g., some with middle names, some without), you may need to clean your data first.
- For names with prefixes (Mr., Dr., etc.) or suffixes (Jr., III, etc.), consider removing these before splitting or handle them separately.
- If the Text to Columns feature is grayed out, ensure your data is not in a table format. Convert it to a range first if necessary.
- For large datasets, consider using Power Query for more advanced name parsing capabilities.