How to Separate First and Last Name in Excel

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.

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.

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.

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.

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

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 or quickly delete all blank cells in Excel.

Read the full article: How to Separate First and Last Name in Excel

from MakeUseOf http://bit.ly/2IlhOmV
via IFTTT