When it comes to manipulating text data, one very common task is how to separate first and last name in Excel.
It is very common to have an Excel worksheet that contains a list of full names in a single column and you may want to separate the first and last names into different columns.
There are a number of ways to separate first and last name in Excel and each method depends on how the data is structured.
In this post, i will share with you three different ways to separate first and last name in Excel and we will cover the following topics:
- How to Separate First and Last Name using Flash Fill
- How to Split Names Using Text to Columns
- Split First, Middle and Last Names using Formulas
- Important Notes to remember
1. How To Separate First and Last Names Using Flash Fill in Excel
Flash Fill is a feature introduced in Excel 2013 that allows you to easily separate text data in excel. Once Excel has identified a pattern, the flash fill functionality will automatically fill data into the cells.
a. Separate First and Last Name
To separate the First and Last Name from the Full Name using Flash fill in excel, follow the steps below:
1. Create a First Name and a Last Name column
2. Type in the first name in the First Name column in cell C2 and press Enter
3. Go to the next cell below and press CTRL + E to fill in the names in the other cells below.
- Type the first name in the first cell. As you are typing in the second cell below, Flash Fill will automatically display a preview of the names listed below in grey font colour. Once you confirm the names listed to be correct, press enter to fill the names in the cells automatically.
In order for Flash Fill to work, there needs to be a pattern it can follow. When you enter the first name in the first cell, Flash Fill will not be able to identify any pattern. You have to do the same in the second or third cell before a pattern is recognized.
If the suggestion is correct, press the enter key to fill in to the cells and if the suggestion is not correct, try entering the first name manually into a few more cells for Excel to pick up the pattern.
However, at times the pattern suggestions may not display as shown above. In this case, you will have to use other methods.
To do this, follow the steps below:
1. Enter the first name manually into two or three cells.
2. Select all the cells and hover the cursor on the bottom-right to get the ‘plus’ sign.
3. Double click on the ‘plus’ sign. It will fill the names in the cells below.
Notice that the names filled are duplicates which makes it incorrect. Therefore, you should continue to the next steps.
4. Go to the bottom-right of the resulting data, and click on the Auto-fill icon.
5. Then select the Flash Fill option
This will give you the result from Flash Fill based on the pattern it deduced.
Also, you can get the middle and last name using Flash Fill. Just enter the middle name or the last name in the first two cells and flash fill will recognize the pattern.
b. Remove Middle Name Using Flash Fill
When names have middle names and you want to extract only the first and the last names, you can also use Flash Fill to do that.
To remove the middle names, follow the steps below:
1. Enter the First and Last names manually in the first cell
2. Type in same first and last name in the second cell. As you are typing, Flash Fill will display the same pattern in light grey font.
3. Confirm the names displayed in grey are correct, then press enter.
c. Extract Only the Middle Name
Just like you removed the Middle names from the full names, you can also extract only the middle name using Flash Fill following the same steps.
Type the middle name in the first and second cells and Flash Fill will display the pattern and fill into the cells when you press the enter key.
However, for this to work, all the names in the list must have a middle name.
2. How to Split First and Last Name using Text to Columns
Text to Columns is a functionality in Excel that allows you to split any text or date data into different columns.
Let’s say you have a dataset with full names in one column, and you want to separate the first and the last names into their different columns as shown above, here are the steps to separate the first and last name in excel using Text to Columns.
1. Select all names in the column. In this example, A2:A20.
2. Go to Data tab -> Text to Columns to open the Convert Text to Columns Wizard dialog box.
3. In the Convert Text to Columns Wizard – Step 1 – 3 dialog box, make sure you select Delimited, then click Next
4. In step 2 of 3, select the Space option.
This is the Delimiters you find in the data. The delimiters helps to identify where the cell content is split from. Also, a data preview section will show you what the content will look like. Then click Next.
5. In Step 3 of 3, select Text option in the Column Data format, then change the cell Destination to where you want the result. If you do not change the cell destination, it will overwrite the existing data.
6. Click Finish
This will instantly split the first and last names into different columns.
However, if there are some names in the list with middle name, the middle names will be entered into the Last Name column and the last name will be entered into the next column beside it. Like this:
In such a case, it is advisable to separate the first, middle and last names using formulas in Excel.
3. Split First, Middle and Last Names using Formulas
You can also use formulas to split text data in excel.
Unlike the Text to Column method where the result is static and the data has to be consistent to be able to get the accurate result of the first name and last name, using formulas will allow you to separate full names that have a middle name into their separate columns.
Also, the result is easily updated when new names are added to the list.
Here, we will use three formulas to separate the first, middle and last names in Excel.
Formula to get the First Name
Enter the formula below into cell C2 then drag down to fill the formula into the cells below.
Based on this formula, the LEFT function extracts all the text that comes before the space (“ ”) while the SEARCH function locates the position of the space character in between the first and last name.
Formula to get the Middle Name
Enter this formula into the Middle Name column:
=MID(B2,SEARCH(” “,B2)+1,SEARCH(” “,SUBSTITUTE(B2,” “,”@”,1))-SEARCH(” “,B2))
Then drag and fill to apply the formulas into the other cells.
This formula uses the MID function to specify the position to start from and the number of characters that is extracted from that position.
Using the MID function, all you need is to have the starting position and the last space character position to be able to easily extract the middle name. With the SEARCH function, you can find the start position. Then you will need to find how many characters you want to extract from the start position to the last space character.
To do this, you use the SUBSTITUTE function to replace the last space character with the ‘@’symbol. Then the SEARCH function is used to find the position of the last space character.
Formula to get the Last Name
Enter this formula in the Last Name column:
In this formula, the RIGHT function is used to extract all text characters on the right that come after the position of the space character (“ ”).
The space character position is first located and then used to find out how many text characters comes after the space by subtracting the space character position value from the total number of characters in the name.
Although, this formula works well to extract the first and last name, it does not work well if the names have a middle name. This is because only one space character is accounted for in the formula, which is between the first and last name.
So it will pick all the names after the first space character as the last name, thereby having the middle and last name in the last name column as shown above.
If a name has a middle name, it means there are more than one space character. And so will require a different formula to be able to extract only the last name.
To extract the last name from names that have a middle name, use the formula below:
=RIGHT(B2,LEN(B2)-SEARCH(“@”,SUBSTITUTE(B2,” “,”@”,LEN(B2)-LEN(SUBSTITUTE(B2,” “,””)))))
The LENGTH function finds the total number of space characters by finding and subtracting the length of name with space character from the length of name without space character.
The SUBSTITUTE function replaces the last space character with an ‘@’ symbol. You can use any other symbol that is not in the name.
The SEARCH function is used to find the position of the @ symbol after it has been substituted.
Now the RIGHT function can extract all the characters to the right of the @ symbol.
One major advantage you get when using formulas to separate names or text in Excel is that the result is dynamic. That is, the result can easily be updated when new names are added to the list or names are changed in the dataset.
4. Important Notes to Remember
When you separate first and last name in Excel using Text to Column, you should remember that:
- The best time to use Text to Column method is when the text data is consistent. That is, when all the names have first and last names only or all names have middle names.
- The result produced is static. This means that, when you make changes to the original data, it does not change or update. And so, if new names added to the list, you will have to go through the steps all over again to separate the names.
- If the destination cell is not specified, Text to Column will overwrite the original data.