How-To

How to Separate Names in Excel

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.

Select Names from List Excel

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

data text to columns

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

text to columns wizard delimited

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

delimiters space

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

clear column select cells

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

select C2 Cell

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

Separate Names in Excel

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

First and last names separated

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.

manually type in first name C2

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

manually type in last name D2

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

Cell Data Tab C2

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

Data Tools Flash Fill Excel

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

Flash Fill First names

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

last name flash fill excel

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

Last Names listed excel

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.

2 Comments

2 Comments

  1. Dirgster

    Hello, Brian,
    I see your Excel tips every day in the newsletter and appreciate your work. I have Excel with MS Office installed but have never used it, although I would like to. Is there a good Excel tutorial that you would recommend? I would appreciate your reply. Thanks!

  2. Yitz

    Excellent tips – many thanks

    How could this work if some people have 1 first name and others have 2

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top