A common task at hand on MS Excel is separating data into components. It’s best to split up data components to make sorting and filtering easier. One real-life scenario is splitting names into first and last names.
4 Ways to Split First and Last Name in MS Excel
- Using Text to Columns
- Using Flash-Fill
- Using Formulas to Get Parts of the Name
- Separate Using Find and Replace
Method 1: Using Text to Columns
This is a quick and efficient way to split data, by making use of the Excel Wizard which guides you through the steps.
Step 1: Select relevant dataset
Insert two new columns where the split data must go.
In our example, we added the headings FIRST NAME and LAST NAME.
Next, select the column with the full names. In our example, that would be Column A.
Once selected, click on ‘Data’ in the top menu bar.
Step 2: Apply ‘Text to Columns’
Within ‘Data’, click on Text to Columns.
A pop-up window will appear, taking you through the steps.
In our example, you will see Excel has already determined that our data is delimited. Click on Next.
In the next pop-up window, select Space in the options for Delimiters. Click on Next.
Now, in the next pop-up window, you can edit your destination as = $B$2:$C$2. Reason being is that cell 1 is the header, so we want the data to start at Cell 2.
You can leave the Column data format as ‘General.’
Click on Finish.
The result is as follows.
Method 2: Using Flash-Fill
This method is useful with even longer names. The key is that Excel replicates the existing pattern of the names.
Step 1: Choose your dataset
Copy the first name part from the full name and paste it into the FIRST NAME column.
Step 2: Apply ‘Flash-fill’
With cell B3 selected, click on the ‘Flash Fill’ icon under the ‘Data’ tab.
Column B will be filled with the first names now.
An alternate way is available from Excel 2013 onwards. When you copy and paste the first name in B3, excel will automatically give you the Flash-fill option.
Now repeat the same steps for the last name.
Copy and paste the last name from cell A2 into cell C2.
Then click on Flash-fill.
Method 3: Using Formulas to Get Parts of the Name
Let’s go over three different formula syntax that we use to extract parts of the names. This is useful for datasets where many names contain a middle name.
The syntax for extracting the first name is as follows:
=LEFT(Cell Reference,SEARCH(” “,Cell Reference)-1)
The syntax for extracting the last name is as follows:
=RIGHT(Cell Reference,LEN(Cell Reference)-SEARCH(” “,Cell Reference))
The syntax for extracting the middle name is as follows:
=MID(Cell Reference,SEARCH(” “,Cell Reference)+1,SEARCH(” “,SUBSTITUTE(Cell Reference,” “,”@”,1))-SEARCH(” “,Cell Reference))
Step 1: Apply the formula you require
We apply the required formula in the cell where we need the data to be filled.
Here, the SEARCH function gets the position of the space character. We use the LEFT function to get all the text before the space position. The result is given below.
To get the last name we will run the following formula:
Here, the SEARCH function gets the position of the space character. We use the RIGHT function to get all the text after the space position. The result is given below.
To extract the last name when we have a middle name, we use the following formula: =RIGHT(A3,LEN(A3)-SEARCH(“@”,SUBSTITUTE(A3,” “,”@”,LEN(A3)-LEN(SUBSTITUTE(A3,” “,””)))))
To extract the middle name, we will use the following formula:
=MID(A3,SEARCH(” “,A3)+1,SEARCH(” “,SUBSTITUTE(A3,” “,”@”,1))-SEARCH(” “,A3))
Method 4: Separate Using Find and Replace
For this method we use a wildcard character. A wildcard character is for example an asterisk (*) which can be used in place of a text.
For example, we can put *A in ‘Find and Replace’ to find all names that start with an A.
Let’s show you how to use this to find the first name:
Step 1: Copy and paste your data
Copy everything in column A and paste it into column B.
Step 2: Use ‘Find and Replace’
Select column B.
Click on the ‘Home’ tab in the main menu bar.
Click on the ‘Find’ icon (a magnifying glass) and then select ‘Replace’.
Now in the dialog box that opens, type a space and a * in the find field and keep the replace field empty. Now click on Replace All. (The space before the * is vital)
You will get a notification about the number of replacements made. Your result will have all the first names.
To find the last name we copy and paste everything from Column A (FULL NAME) into the LAST NAME column.
Again, select the ‘Find’ icon and then select ‘Replace.’
We then modify the fields in the dialog box that opens up on clicking Replace.
In the dialog box that opens, type a * and space in the find field and keep the replace field empty. Now click on Replace All. (The space after the * is vital)
Now column C will have all the last names.
Now, let’s say your data set has middle names that you would like to remove. We select the column with the full name. With column Aselected, we click on Find and click on Replace.
In the pop-up window, we put in space * space in Find field.
And put a space in the Replace with field.
Click on Replace All.
You get the result as names with middle names removed.
NOTE: If you still want to keep a record of the original data with the middle names, copy your FULL NAME column to a new column first before removing the middle names.
We hope that all these methods were able to help you out and saved you loads of time! Let us know your thoughts in the comments.