Microsoft Excel Logo.

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.

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.

An Excel spreadsheet with full names of people.

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.

Select all names in the spreadsheet.

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.

Click "Text to Columns" in the "Data" tab in Excel.

Advertisement

A “Convert Text to Columns Wizard” window will open. Here, we’ll select “Delimited” and then click “Next.”

Select "Delimited" and click "Next" on the "Convert Text to Columns Wizard" window.

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.”

Tip: 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).

Select "Space" in the "Delimiters" section and click "Next."

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.”

Click "Finish" at the bottom of the "Convert Text to Columns Wizard" window.

And that’s all. The first and last names are now separated from your full name cells.

First and last names separated in Excel.

RELATED: How to Use Text to Columns Like an Excel Pro

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.

Advertisement

To demonstrate the use of Flash Fill, we’ll use the following spreadsheet.

First, middle, and last names in an Excel 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.”

Tip: 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.

Click the C2 cell and manually enter the first name.

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.

Click the D2 cell and manually enter the last name.

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.

Click the "Data" tab in Excel's ribbon.

In the “Data” tab, from under the “Data Tools” section, we’ll select “Flash Fill.”

Select "Flash Fill" in the "Data" tab in Excel.

Advertisement

And instantly, Excel will automatically separate the first name for the rest of the records in your spreadsheet.

First names separated in Excel.

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.

Last names separated in Excel.

And that’s how you go about rearranging the names in your Excel spreadsheets. Very useful!


Like this, you can quickly turn a long column into multiple columns with a useful Excel feature.

RELATED: How to Make One Long Column into Multiple Columns in Excel

Profile Photo for Mahesh Makvana Mahesh Makvana
Mahesh Makvana is a freelance tech writer who specializes in writing how-to guides. He has been writing tech tutorials for over a decade now. He’s written for some of the prominent tech sites including MakeUseOf, MakeTechEasier, and Online Tech Tips.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.