How-To

How to Separate Names in Excel

By Brian Burgess

Published December 29, 2021

If you have an Excel spreadsheet with first and last names in a single cell, you might want to separate them. Here’s how.

What do you do when a spreadsheet comes in that needs to be cleaned up? For example, you might need to separate names into two separate columns versus one. Or, what about having first, middle, and last names in one column? If you run across such a situation, Microsoft Excel 2013 and above has excellent built-in tools that allow you to separate them.

If you need to separate names in Excel, follow this guide, and we’ll show you how.

Separate Names in Excel

To separate first and last names into different columns in Excel, do the following:

1. Select all of the full names from the sheet you want to separate. Make sure not to select any headers – just the names.

2. Click on the Data tab on the ribbon and then click Text to Columns.

3. The Convert Text to Columns Wizard will start. Check Delimited and click Next.

4. On the following screen of the wizard, in the Delimiters list, check Space and uncheck any other Delimiters. You’re checking Space because a single space separates the names in the selected rows. Click Next.

5. Now, choose where to display the separated first and last names in the spreadsheet. Start by clicking the Destination field and clearing its content. Then click the up-arrow icon to select the cells to display the names.

6. For this simple sheet, we want to display the first name in the C column and the last name in the D column. Select the C2 cell on the spreadsheet and click the down-arrow icon.

7. The Convert Text to Columns Wizard opens again and will display the proper destination cells. Click the Finish

8. The first and last names will populate your spreadsheet and are now separate. Continue working on your document, or save your work for later.

Separate First and Last Names with Middle Names in Excel

Separating first and last names is straightforward enough, but what if there are middle names too? Good news. You can use the Flash Fill feature in Excel 2013 or above. Unfortunately, the Flash Fill feature isn’t part of older versions of Excel.

You can separate first and last names with middle names in Excel, do the following:

1. Open the spreadsheet with the names you want to sort. Click the cell where you want to display the first name. In the following example, it’s C2. Then manually type in the first name. in this case, it’s Brian.

2. In the D2 cell, manually type the last name of the B2 record – in this instance, it’s Burgess.

3. Now it’s time to activate Flash Fill. Click the C2 cell where you manually typed in the first name and select the Data tab on the ribbon.

4. From the Data tab, click the Flash Fill button in the Data Tools

5. Excel will immediately separate the first name from the middle and last and fill them into the spreadsheet section you chose.

6. Since that worked so well, let’s do the same thing for the last names. Click the cell where you manually typed in the last name — in this example, it’s D2. On the ribbon, click the Data tab and click the Flash Fill button in the Data Tools section.

7. Excel automatically populates the D column with the last names and separates them from the first and middle names.

That’s all there is to it. We presented a basic spreadsheet, but the instructions will work with the most extensive and complex workbooks. If you need to separate names in Excel, follow the guide above, and you’ll be set.

Excel has several cool features like Custom Sort and Conditional Formatting. You can also visually represent your data by creating a pie chart or using a scatter plot.