In Excel, it is very easy to split first, middle, and last names that appear in the same column into separate columns by using different ways.
Suppose you received a contact list with all the names listed in full names in a single column and you’ll probably need to separate the first, middle, and last names, and split them into separate columns. There are few different ways you can use to separate names – by using the Text to Columns feature, Flash fill, and formulas.
In Excel, it’s possible to parse data from one column into two or more columns. In this article, we will show you how to split the names into different columns in excel by using different ways.
How to Split Names in Excel
There are three easy ways to split names in Excel. The method you choose will depend on how your data is structured and whether you want the split data to be static or dynamic.
- Split names using Text to Columns feature
- Separate names using Formulas
- Separate names using Flash Fill
Separate Names Using Text to Column Wizard
The Text to Column Wizard is the easiest way to separate first and last names in Excel in addition to middle names. Make sure that there are empty columns next to the names you’re going to split because the full names will be split into separate columns.
For example, you have the below dataset with full names and you want to separate the first and last names and get these into separate cells.
First, highlight the column of full names that you want to separate. Then, go to the ‘Data’ tab and click on the ‘Text to Columns’ option in the ‘Data Tools’ section.
The Convert Text to Columns Wizard will appear. On the first step of the Convert Text to Columns Wizard, select the ‘Delimited’ option and click ‘Next’.
In Step 2 of 3 of Convert Text to Columns Wizard, select the delimiter that separates your data, remove any other checkmarks, and click ‘Next’. In our case, ‘space’ separates the First and Last names, so we choose this delimiter.
The Data preview section below in the window shows how your names are parsed.
On step 3 of 3, you select the data format and destination and click ‘Finish’.
Normally, the default ‘General’ will work just fine for most types of data. In the ‘Destination’ field, specify the location where you want the output to be displayed. You have to select the topmost cell in the column where you want to output the results (B2, in our case).
Please keep in mind that if you don’t specify the destination, this will overwrite the original data, so be sure to choose an empty column.
Once you click on the ‘Finish’ button, it would instantly separate the full names into two separate columns (First Name and Last Name).
Follow the same steps if you have first, middle and last names, and your names will be split into three columns instead of two.
Note: The result of this method is static. That means that in case you change the original data, you need to do this all over again to split the names.
Split Names Separated by Commas
If the first and last names are separated by commas, you can follow the steps below to remove Comma and separate First and Last Names.
In the below example, names are formatted in reverse format (Last Name, First Name), where the last name comes first followed by a comma and then the first name.
Select the names and go to Data –>Text to Column. On step 1, choose the ‘Delimiter’ and click ‘Next’. In step 2, under Delimiters, check ‘Comma’ (,) as your delimiter because your names are separated by a comma.
On the last step, you choose the data format as ‘General’, specify the destination, and click ‘Finish’.
Now, you will have names in separate columns.
Separate Names Using Formulas
The Text to Columns wizard is quick and easy to separate names. However, if you plan to edit the original names and are looking for a dynamic solution that will update automatically, you’d better split names with formulas. You can use LEFT, RIGHT, MID, LEN, and SEARCH or FIND functions to separate names.
Separate First and Last Name in Excel Using Formulas
Get the First Name
Let’s say you have the dataset as shown below and you want to separate the first name into a cell. You need to combine FIND and LEFT function into one formula to get the first name.
Use the below formula to get the first name:
The above formula uses the FIND function (you can also use the SEARCH function) to find the position of the space character (“ “) in between the first and last name and subtracts 1 to exclude the space itself. This number is then supplied to the LEFT function, which uses this position number to extract all the text before it.
After you enter the formula in an empty cell (B2), drag the fill handle down to other cells to apply this formula, and all first names have been split into column B as shown below:
You can either nest the SEARCH and FIND function inside the LEFT function to extract the first name. Essentially, the only difference between the Excel SEARCH and FIND functions is that SEARCH is case-insensitive, while FIND is case-sensitive.
If you have comma (,) instead of space between the first and last name, then use comma as the first argument in the FIND function:
Get the Last Name
Now if you need to extract the last name, use the RIGHT function. The below formula will extract the last name from the same dataset:
The formula first finds the space character position, subtract that number from the total length of the string (which is returned by LEN), and this number is then used in the RIGHT function to extract that many characters from the right side of the string (name).
Separate First, Middle, and Last Name in Excel Using Formulas
Separating names that include a middle name or middle initial requires slightly different approaches, depending on the name format.
To get the first name when you have a middle name as well, use the same LEFT FIND formula that you already familiar with.
Get the Last Name
The above RIGHT FIND formula works well when there is only the first and last name, it wouldn’t work in case your original names contain a middle name or middle initial. This is because you didn’t account for two space characters in the name.
To get the last name when you have a middle name as well, use this formula:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))
To extract the last name, first determine the position of the second space character by using the nested SEARCH functions, add 1 to SEARCH(” “,A2,1) to start the extraction with the next character. Next, subtract the position of the 2nd space from the total string length, and get the length of the last name as result. Then supply the resulting number to the RIGHT function to extract the number of characters from the end of the string.
Get the Middle Name
The MID function uses three arguments, which allows you to specify the text, start position and the number of characters to extract the middle name from that position.
=MID(text, start_num, num_chars)
To get the middle name, enter this formula into a blank cell:
=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)
Lets see how this complex formula works:
To extract the middle name, you need to determine the position of both spaces in the name. To find the position of the first space, enter this
SEARCH(" ",A2) function in the ‘start_num’ argument and add 1 to start the extraction with the next character.
Then, to find out the length of the middle name put this
SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1 nested function in the ‘num_chars’ argument, which subtracts the position of the 1st space from the position of the 2nd space, and subtracts 1 from the result to get rid of a trailing space. This will give you how many characters to extract.
Now, the MID function with starting position of middle name and number characters to extract separates the middle name from the full name (A2).
Separate Names in Excel Using Flash Fill
Flash fill is used to automatically fill data of a specific pattern. It can also be used to split names. It is only available in Excel 2013, 2016, 2019, and 365.
Let’s say you have a dataset as shown below and you want to get only the first name.
In the adjacent cell to the original name, type the first name from the full name. In this case, type ‘Steve’ in cell B2.
Then start typing the first name in the second cell. While you’re typing, if Excel senses a pattern, Flash Fill will show you a list of the first name in other cells automatically (in grey).
When you see the names in grey and if those names are right, then just hit the ‘Enter’ key and Flash Fill will automatically fill the rest of the cells with first names.
Follow the same procedure to separate last names into a separate column.
Flash Fill works by detecting a pattern in that data that it can follow when giving you the modified data. In our example, when you enter the first name in the first cell, Flash Fill doesn’t recognize the pattern. But as soon as you start typing the first name again in the second cell, Flash Fill recognizes the pattern and shows you the suggestion for splitting first names. If the suggestion is correct, just press the ‘Enter’ key.
Usually, the Flash Fill feature is enabled by default. If it does not work in your Excel, after typing the first name in the first cell, you can simply select the second cell and click the ‘Flash Fill’ button from the Data Tools group on the ‘Data’ tab.
Alternatively, you can also press ‘Ctrl’ + ‘E’ on your keyboard to achieve the same results.
Immediately, the rest of the cells will be filled out with first names.
Sometimes, you may not see the pattern in grey, in that case, you can do this to get the Flash Fill result.
First, type the text manually in two cells and select both these cells. Then, Hover your cursor on the bottom-right part of the selection. You will notice that the cursor changes from a small green square (Filler icon) to a plus icon.
Next, double click on that plus icon. This will fill the rest of the cells. At this point, the results are incorrect, you’ll see both first names are repeated again and again. Then, at the bottom right corner of the resulting data, you will see a small Auto-Fill icon like it is shown below. Click on this ‘Auto-fill’ icon and select ‘Flash Fill’.
This would fill first names in all the cells:
Remove the Middle Name
You can also use the Flash Fill tool to get rid of the middle name from full name.
For example, let’s say you have the below dataset and you want to get only the first and the last name without the middle name or middle initial.
To get names without a middle name or middle initial, manually type ‘Lord Stark’ in the adjacent cell. Then, in the second adjacent cell, type ‘Daenerys Targaryen’. While you’re typing, Flash Fill will recognize a pattern and it will show you a list of names without middle names (in grey).
If these names are right, press the ‘Enter’ key and Flash Fill will automatically fill the rest of the cells with the names without the middle name.
If you want to get only the middle names without first and last names, type the middle name in the first two cells and use the Flash Fill tool to get the names from all the names in a column.
This tutorial shows how to separate names while manipulating the text data. You can also use the same methods to work with other forms of data such as addresses, product names, etc.