Quick Links
Have you got a list of full names that need to be divided into first and last names in separate columns? It's easy to do that, thanks to Microsoft Excel's built-in options. We'll show you how to perform that separation.
Table of Contents
- Separate First and Last Names When There Are No Middle Names
- Separate First and Last Names When There Are Middle Names
How to Split First and Last Names Into Different Columns
If your spreadsheet only has the first and last name in a cell but no middle name, use Excel's Text to Columns method to separate the names. This feature uses your full name's separator to separate the first and last names.
To demonstrate the use of this feature, we'll use the following spreadsheet.
First, we'll select all the full names that we want to separate. We will not select any column headers or Excel will separate them as well.
In Excel's ribbon at the top, we'll click the "Data" tab. In the "Data" tab, we'll click the "Text to Columns" option.
A "Convert Text to Columns Wizard" window will open. Here, we'll select "Delimited" and then click "Next."
On the next screen, in the "Delimiters" section, we'll select "Space." This is because, in our spreadsheet, the first and last names in the full name rows are separated by a space. We'll disable any other options in the "Delimiters" section.
At the bottom of this window, we'll click "Next."
If you have middle name initials, like "Mahesh H. Makvana," and you want to include these initials in the "First Name" column, then choose the "Other" option and enter "." (period without quotes).
On the following screen, we'll specify where to display the separated first and last names. To do so, we'll click the "Destination" field and clear its contents. Then, in the same field, we'll click the up-arrow icon to select the cells in which we want to display the first and last names.
Since we want to display the first name in the C column and the last name in the D column, we'll click the C2 cell in the spreadsheet. Then we'll click the down-arrow icon.
At the bottom of the "Convert Text to Columns Wizard" window, we'll click "Finish."
And that's all. The first and last names are now separated from your full name cells.
Separate First and Last Names With Middle Names
If your spreadsheet has middle names in addition to first and last names, use Excel's Flash Fill feature to quickly separate the first and last names. To use this feature, you must be using Excel 2013 or later, as earlier versions don't support this feature.
To demonstrate the use of Flash Fill, we'll use the following spreadsheet.
To start, we'll click the C2 cell where we want to display the first name. Here, we'll manually type the first name of the B2 record. In this case, the first name will be "Mahesh."
You can use Flash Fill with middle names, too. In this case, type the first and the middle name in the "First Name" column and then use the Flash Fill option.
We'll now click the D2 cell and manually type the last name of the record in the B2 cell. It will be "Makvana" in this case.
To activate Flash Fill, we'll click the C2 cell where we manually entered the first name. Then, in Excel's ribbon at the top, we'll click the "Data" tab.
In the "Data" tab, from under the "Data Tools" section, we'll select "Flash Fill."
And instantly, Excel will automatically separate the first name for the rest of the records in your spreadsheet.
To do the same for the last name, we'll click the D2 cell. Then, we'll click the "Data" tab and select the "Flash Fill" option. Excel will then automatically populate the D column with the last names separated from the records in the B column.
And that's how you go about rearranging the names in your Excel spreadsheets. Very useful! If you ever need to do the opposite, learn the many ways you can combine first and last names in Excel.
Like this, you can quickly turn a long column into multiple columns with a useful Excel feature.