7
 min read

How to separate first and last names in Excel

Every way you can split names into first and last name in Excel.

You just downloaded a CSV and are ready to upload it to your email list. But, you discover that first and last names are merged into a full name column and you need first names separated out for your email blast. Don't worry! We'll show you how to separate first and last names from a single column in Excel, and how to handle edge cases you might run into. We will also show you how to split first and last names in OneSchema in a single click.

There are two approaches. We will look at each of these in detail.

1. Using Excel Menu options 

2. Using Excel Formulas

Using the Excel Menu option is easier than Formulas, but the latter is more flexible. It can handle scenarios like if your text has more than one character as a delimiter. 


Method 1: Separate First and Last Names Using Excel Menu Options


Image


Step 1

Click on the top of the column next to the existing list of names, then select Insert to add an empty column. Repeat it so that you now have two empty columns.

If your data also contains a middle name, add three columns to store your final data.

Image

Step 2 

Choose the column with the data you want to split. In our example, it's the Name column. Select the data alone, not the header.

Image

Step 3

Now go to the Data menu and select the Text to Columns option. The Text to Column wizard will open.

Image

Step 4

Choose the Delimited radio button in the Original data type section. Now click Next.

Image

Step 5 

Choose the Delimiter that separates your data in the Delimiters section. In my example, a space separates the First Name and Last Name. If two words have more than one space, make sure you check the box next to the phrase, “Treat consecutive delimiters as one."


Click Next if the data in the Data preview section looks correct.

Image

Step 6

The next screen lets you select each column and select the data format. In this example, the Column data format is Text or General. By default, it will be General. Repeat for the second column too.

Image

Step 7

Next, select Destination. This indicates the row and column where your split data will appear. In our example, we had selected Column B as the destination, so we see $B$2 as the destination for the first name.

Image

Step 8 

Now click on Finish to see the results of your splitting.

Image

Image

You can delete the original name column if you don't need the full name.


Downsides of using Excel Menu options to separate first and last names

  • If the names aren't in a consistent format, it's difficult to identify a pattern for the first name and last name.
  • If a prefix or suffix is also present, it will have to be manually removed.
  • Compound names without hyphens like David Lloyd George require a manual update to put both parts of the name in either the first or last name columns.
  • To support middle names, you need to either include them in the First Name column or add them as a custom field during the import process.


Method 2: Separate First and the Last Name Using Excel Formulas

Instead of using the Text to the Column menu option, we can achieve the same results using functions like SEARCH, LEFT, and RIGHT in Excel

Step 1 

We have to Insert two columns and label them First Name and Last Name

First, we will use a combination of SEARCH and LEFT to extract the first name.

In Cell B2, enter =LEFT(A2,SEARCH(" ",A2)-1).

Here's what's happening. SEARCH will search for the space delimiter in A2 and return the location of the space. We pass that as the second parameter to LEFT, a function that returns the string from start to the provided position. So, the results is the string till the first space, which is the first name.

For example, with the name Kimaya Donister, this nested formula behaves the following way:

=SEARCH(" ",A2): this returns 7

=LEFT(A2,7-1): this second parameter is the number of characters to read which is the first 6 characters, or Kimaya.

Image

Step 2

Similarly, we will use a combination of SEARCH , RIGHT , and LEN to extract the last name. In Cell C2 enter:

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

Here's how this second function works. Here, SEARCH will work in the same way. Instead of LEFT, the RIGHT function reads the string from the right side. LEN is used to get the total length of the original text. It reads A2 from the right side. Taking this length and subtracting the value returned by the SEARCH function give you the number of characters to read from the right.

This nested formula is executed as follows:

=SEARCH(" ",A2) : this statement returns 7

=RIGHT(A2,15-7): 15 is total length - 7, the location of space, gives you 8. The last 8 characters are Donister.

Image

Step 3

Drag the sizing handle in cells B2 and C2's lower right corners to copy the formula for all the provided names.

Image


Separate Middle Name and Initials

Step 1

If the name field also contains the middle name or initials, we have to insert one more column after the first name. Label it Middle Name

Step 2

Use the formula given below to extract the middle name if it is present or otherwise leave it blank.

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

Here, I have used IFERROR so that if the middle name is not present in some data, it will just insert a space in the Middle Name column.

The MID function extracts the substring from the middle. This function has three parameters - Original string, Starting Number, Number of chars.

Image

Use the formula given below to extract the last name:

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

Image

Step 3

Drag the sizing handle in the lower right corner of cell C2 to apply the formula to other cells. You can easily separate first, middle and last names using this method.


Separate the prefix from the name

If the name field contains a prefix along with the first and last name, follow the steps given below:

Step 1

Insert three columns called Prefix, First Name, and Last Name. Label them in order.

Step 2

In Cell B2, use this formula to extract the prefix:

=IF(OR(IFERROR(SEARCH("Mr.",A2),0),IFERROR(SEARCH("Mrs.",A2),0),IFERROR(SEARCH("Ms.",A2),0)),LEFT(A2,SEARCH(" ", A2) -1),"  ")

Image

Step 3

In Cell C2, use this formula to extract the first name:

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

Image

Step 4

In Cell D2, use this formula to extract the last name:

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

Image

Step 5

Drag the sizing handle in the lower right corner of cells B2, C2, and D2 until the data exists to copy the formula.

Image


Separate First and Last Name Using the Flash Fill Option

The Flash Fill option is only available in the Microsoft Office 2013 and up. Follow these steps to split the cells:

Step 1 

Open the Excel file that contains the data you would like to separate.

Image

Step 2

Select a cell where you'd like to list the first names. Manually type the first name of the first record. In our example, that's Kimaya.

Select Flash Fill from the menu under the Data section.

Image

Excel will automatically populate the first names the rest of the records of your file.

Image

Step 3

In the next cell, manually type the last name of the first record of your data.

Now, select Flash Fill. Excel will automatically populate the last names on the rest of the records of your file, and you can easily separate first and last names using the Flash Fill method

Image


Separating First and Last Names in OneSchema

OneSchema has a built-in tool for separating first names and last names. From the dropdown menu, you can select "Column transformations" > Split to columns> Full Name. Your names will be split into a first and last name column.

Want receive the best maketing insights? Subscribe now!

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Massa adipiscing in at orci semper. Urna, urna.

Thanks for joining our newsletter.
Oops! Something went wrong.