How to Separate First and Last Name in Excel
Whatsapp Pinterest
Advertisement

When you’re working with data in your Microsoft Excel workbook, it’s not always just numbers. Maybe your spreadsheet contains names of customers, clients, employees, or contacts. And depending on where your data comes from, you may have to manipulate it to suit your needs for a mailing list or database.

If you need to separate first and last names in Excel, you have some flexible options. This includes not only first and last name, but middle name, prefixes, and suffixes. Here’s a helpful guide that shows you how to split names in Excel.

How to Separate Names in Excel with the Wizard

The Text to Column Wizard is the simplest way to separate first and last names in Excel in addition to middle names. And the tool is flexible enough to adjust to your data.

Make sure that there is an empty column next to the data you are going to split because the results will go there by default. If this isn’t possible, you still will be able to change the destination for your data split.

Convert Text To Columns Wizard in Excel

Split Names Separated by Spaces

First, if the names are separated by spaces then just follow these steps.

  1. Select the column or cells containing the names you want to separate. The column or cells will be highlighted.
  2. Click the Data tab and select Text to Columns in your ribbon.
  3. In the popup window, choose Delimited for the file type that best describes your data.
  4. Click Next.
  5. Under Delimiters, uncheck Tab and check Space.
  6. Click Next.
  7. Under Column data format, choose Text. If you need to select a different Destination for your results, enter it in that field.
  8. Click Finish.

Convert Text To Columns Wizard - Separate Names

This method works for separating the first and last name in Excel as well as the middle name or initial. So, if your data is in the cell as Sue Sally Smith, Sue S. Smith, or Sue S Smith, each will work correctly.

Split Names Separated by Commas

If the first and last names are separated by commas, you’ll only need a slight adjustment to the instructions above. In Step 5, under Delimiters, check Comma. You can keep Space checked if the data also has spaces.

Convert Text To Columns Wizard - Comma Separated

Split Names and Remove Data

Let’s say you have names that include first, middle, and last name but you don’t want to keep the middle name. Start with the same steps as above until you get to Step 7, then do the following.

  1. Under Column data format, select Do not import column (Skip).
  2. In the Preview of selected data, click to highlight the column of data you want removed from your results.
  3. Click Finish.

Convert Text To Columns Wizard - Remove Data

How to Separate Names in Excel with Functions

If you’re doing a one-time split, the above methods using the Text to Columns Wizard are simplest. But if you will be adding more data that you wish to split, you may want to use Excel formulas 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More . In addition, while the Wizard is flexible, it does have its limits. So, we’ll cover some functions for special circumstances too.

Split First, Middle, and Last Name

Select the cell where you want the data to display and insert or paste in the following formulas:

First Name:

=LEFT(A2,FIND(" ",A2,1)-1)

Last Name:

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))

Middle Name:

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))

Replace the cell labels (letter and number combination) with those for the cells you are splitting.

Names with a Prefix, Remove the Prefix

First Name:

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))

Last Name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

Names with a Suffix, Suffix in Separate Column

First Name:

=LEFT(A2, SEARCH(" ",A2,1))

Last Name:

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))

Suffix:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

Two-Part First Names

First Name:

=LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

Last Name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

Two-Part Last Names

First Name:

=LEFT(A2, SEARCH(" ",A2,1))

Last Name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

Three-Part Last Names

First Name:

=LEFT(A2, SEARCH(" ",A2,1))

Last Name:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

Once again, remember to replace the cell labels with those for the cells you are splitting.

Fill Your Formulas

Once you enter any of the functions above, you can drag to fill the rest of your column with those formulas.

Select the first cell with the formula. Move to the bottom right corner and when the black plus sign appears, drag downward to fill the number of cells you need.

Autofill Formulas in Excel by Dragging

It’s Easy to Separate Names in Excel

If you’re able to use the Text to Column Wizard, then you’ll be able to split first and last name in Excel easily. But if you have some tough data to work with, at least you have options for separating names with functions.

For additional Excel tutorials like this, take a look at how to merge and split cells How to Merge and Split Cells in Microsoft Excel How to Merge and Split Cells in Microsoft Excel There's a reason why the smallest unit in an Excel spreadsheet is called a cell: you cannot divide it any further. Yet, here's how to merge and split cells as you will. Read More or quickly delete all blank cells How to Quickly Delete All Blank Cells in Excel How to Quickly Delete All Blank Cells in Excel Got an Excel spreadsheet that's full of blank rows and/or columns? Don't waste time deleting them by hand. This method makes it easy. Read More in Excel.

Explore more about: Microsoft Excel, Microsoft Office 2016, Microsoft Office 2019, Spreadsheet.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. mma173
    April 9, 2019 at 6:48 pm

    Flash Fill also works fine and is easier.

  2. Josh
    April 9, 2019 at 1:41 am

    This is just a bad idea in the direct marketing world. Our company has spent 30 years refining our name parsing algorithms and Excel will not even come close to the accuracy we need.