Download Excel Workbook
Watch our free training video on Separating Names:
You’re tasked to send out invites to the company barbecue based on the employees’ first names. To do this, you’re going to have to know how to split data such as names into different columns. In this article, we will guide you through the process of How to Separate Names in Excel, using various methods depending on your needs:
Let’s look at these methods thoroughly!
Download the Excel Workbook below to follow along and understand How to Use Substrings in Microsoft Excel –
download excel workbookSplit-Names.xlsx
Using Text to Columns
Excel’s Text to Columns is a built-in tool that can quickly split names based on a chosen delimiter, such as a space or a comma. To use this feature, follow these steps:
Open your spreadsheet and select the cells in the column containing the full names that you want to split.
Go to the Data tab and click on the Text to Columns button. This will open the Convert Text to Columns Wizard dialog box. Choose the Delimited option if your names are separated by a specific character like a space or a comma.
If you select the Delimited option, specify the delimiter that separates your names. In our case, check the Space option because the names are separated by a space. (If the names are separated by a comma, check the comma checkbox) Make sure the preview looks correct.
Choose the format for each column. If you only want to separate the names and don’t need any additional formatting, you can leave the default options as they are.
Click Finish.
Excel will split the names into separate columns based on your chosen delimiter.
Note: It is important for the columns to the right to be empty in order for the new columns to have a destination.
Using Formulas
The formula method is a more flexible approach if you have specific requirements for separating names. Follow these steps to use the LEFT, RIGHT, FIND, LEN functions:
LEFT formula
Insert two new columns next to the column containing the full names. These columns will hold the first names and last names, respectively.
In the first cell of the first name column, enter the following formula:
=LEFT(A2, FIND(” “, A2)-1)
This formula uses the LEFT function to extract characters from the left side of the cell value and the FIND function to locate the position of the space character that separates the first and last names. Pull down on the bottom right-hand corner of cell B2 to apply the formula to the rest of the cells.
RIGHT formula
In the first cell of the last name column, enter the following formula:
=RIGHT(A2, LEN(A2)-FIND(” “, A2))
This formula uses the RIGHT function to extract characters from the right side of the cell value and the FIND function to locate the position of the space character. Pull down on the bottom right-hand corner of cell C2 to apply the formula to the rest of the cells.
The LEN function calculates the length of the cell value, and the subtraction determines the number of characters to extract from the right side.
By using these formulas, you can customize the splitting process further. For example, if your names are separated by a comma instead of a space, you can modify the formulas accordingly.
Using Flash Fill
Flash Fill offers a simple and interactive way to split names in Excel without the need for complex formulas or additional tools.
Follow these steps if you prefer a straightforward solution for basic name separation:
Ensure that you have a column containing the full names that you want to split. In our case, the full names are in Column A.
In a new column next to the column with the full names, enter the desired format for the separated names. For example, if you want to separate the full names into first name and last name, enter the desired format in the adjacent column. In this case, you can enter the first name of the first person manually.
Select all the cells underneath cell B2. Go to the Data tab in the Excel ribbon, select the Flash Fill Button. Excel will detect the pattern based on your input and will automatically fill in the separated names for the remaining cells based on the pattern it recognized. Do this for the last name column, as well.
Once you are done separating the names, you can remove the column containing the original full names if it is no longer needed.
There you have it! Excel is an incredibly powerful tool for splitting names. When you need to separate first names and last names for further analysis, Excel provides various functions and techniques to accomplish this task efficiently.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
