Suppose you received a contact list with all the names listed in full names in a single column and you’ll probably need to separate the first, middle, and last names, and split them into separate columns. There are few different ways you can use to separate names – by using the Text to Columns feature, Flash fill, and formulas.

In Excel, it’s really easy to split names from one column into two or more columns. In this article, we will show you how to split the names into different columns in excel by using different ways.

How to Split Names in Excel

There are three easy ways to split names in Excel. Depending on the data structure and whether you want the split names to be static or dynamic, choose one of the following methods:

  • Split names using Text to Columns feature
  • Separate names using Formulas
  • Separate names using Flash Fill

Separate Names Using Text to Column Wizard

The Text to Column Wizard is one of the most common ways to separate first and last names in Excel as well as middle names. Make sure that there are empty columns next to the names you’re going to split because the full names will be split into separate columns.

For example, you have the below dataset with full names and you want to split/separate the first and last names and store them into separate cells.

First, highlight the column of full names that you want to separate. Then, go to the ‘Data’ tab and click on the ‘Text to Columns’ option in the ‘Data Tools’ section.

The Convert Text to Columns Wizard will open up. On the first step of the Wizard, select the ‘Delimited’ option and click ‘Next’.

In Step 2 of 3 of Convert Text to Columns Wizard, select the delimiter that separates your data, remove any other checkmarks, and click ‘Next’. In our case, ‘space’ separates the First and Last names, so we choose this delimiter.

The Data preview section below in the window shows how your names are parsed.

On step 3 of 3, you select the data format and destination and click ‘Finish’.

Normally, the default ‘General’ will work just fine for most types of data. In the ‘Destination’ field, specify the location where you want the output to be displayed. You have to specify the address of the first cell in the column where you want the results (B2, in our case).

Remember, if you don’t specify the destination cell, the wizard will overwrite the original data, so make sure to choose an empty column.

Once you click on the ‘Finish’ button, it would instantly separate the full names into two separate columns (First Name and Last Name).

Follow the same steps if you have first, middle and last names, and your names will be split into three columns instead of two.

Note: The result of this method is static. It means, if you change the original name, you need to do this all over again to split the names.

Split Names Separated by Commas

If the first and last names are separated by commas, follow the below steps to remove comma and split first and last Names.

In the following example, names are formatted in reverse format (Last Name, First Name), where the last name comes first followed by a comma, then followed by the first name.

Select the names and go to Data –>Text to Column. On step 1, choose the ‘Delimiter’ and click ‘Next’. In step 2, under Delimiters, check ‘Comma’ (,) as your delimiter because your names are separated by a comma.

On the last step, you choose the data format as ‘General’, specify the destination, and click ‘Finish’.

Now, you will have names in separate columns.

Separate Names Using Formulas

The Text to Columns wizard is quick and easy to separate names. However, if you want to edit the original names and want a dynamic method that will update automatically every time you change the names, then splitting names with formulas is the right choice. You can use LEFT, RIGHT, MID, LEN, and SEARCH or FIND functions to separate names.

Separate First and Last Name in Excel Using Formulas

Get the First Name

Let’s say you have the below dataset and you want to separate the first name into a separate cell. You need to combine FIND and LEFT function into one formula to get the first name.

Use the following formula to get the first name:

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

This formula uses the FIND function to find the position of the space character (“ “) in between the first and last name and subtracts 1 to exclude the space itself. This number is then supplied to the LEFT function, which uses this position number to extract all the text before it. You can also use the SEARCH function instead of the FIND function.

After you enter the formula in an empty cell (B2), drag the fill handle down to other cells to apply this formula, and all first names have been split into column B as shown below:

You can either nest the SEARCH and FIND function inside the LEFT function to extract the first name. The main difference between the two functions is that FIND is case-sensitive, while SEARCH is case-insensitive.

If you have comma (,) instead of space between the first and last name, then use comma as the first argument in the FIND function: 

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

Get the Last Name

Now if you need to extract the last name, use the RIGHT function. The following formula will extract the last name from the same dataset:

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

The formula first finds the space character position, subtract that number from the total length of the string (which is returned by the LEN function), and this number is then supplied to the RIGHT function to extract that many characters from the end of the string (name).

Separate First, Middle, and Last Name in Excel Using Formulas

Splitting names that include a middle name needs different formulas, depending on the name format you have.

To get the first name when you have a middle name or middle initial, use the same LEFT FIND formula that you already familiar with.

Get the Last Name

The above RIGHT FIND formula works well when there is only the first and last name, it won’t be of much use in case your original names contain a middle name or middle initial. This is because you didn’t account for two space characters in the name.

To get the last name when you have a middle name as well, use this formula:

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

To extract the last name, first determine the position of the second space character by using the nested SEARCH functions, add 1 to SEARCH(” “,A2,1) to start the extraction with the next character. Next, subtract the position of the 2nd space from the total string length, and obtain the length of the last name as a result number. Then give this resulting number to the RIGHT function to extract the number of characters from the end of the string.

Get the Middle Name

The MID function uses three arguments, the first argument specifies the text or cell address, the second specifies the start position, and the last argument tells numbers of characters to extract the middle name from that position.

The Syntax:

=MID(text, start_num, num_chars)

To get the middle name, enter this formula into a blank cell:

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

Lets see how this complex formula works:

To extract the middle name or middle initial, you need to determine the position of both spaces in the full name. To find the position of the first space character, enter this SEARCH(" ",A2) function in the ‘start_num’ argument and add 1 to start the extraction from the next character.

Then, to find out the length of the middle name put this SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1 nested function in the ‘num_chars’ argument, which subtracts the position of the 1st space from the position of the 2nd space, and subtracts 1 from the result to remove a trailing space. The final result will tell you how many characters to extract.

Now, the MID function with starting position of middle name and number characters to extract separates the middle name from the full name (A2).

Separate Names in Excel Using Flash Fill

Flash fill is mostly used for automatically filling data of a specific pattern. It can also be used to split names. It is only available in Excel 2013, 2016, 2019, and 365.

Let’s say you have the below dataset and you want to get only the first names out of the full names.

In the adjacent cell to the original name, type the first name. In this case, type ‘Steve’ in cell B2.

Then start typing the first name in the second cell of the column. While you’re typing, if Excel senses a pattern, Flash Fill will show you a list of the first name in other cells automatically (in grey).

When you see the list of names in grey and if those names are right, then just hit the ‘Enter’ key and Flash Fill will automatically fill the rest of the column with first names.

Follow the same procedure to separate last names into a separate column.

The result:

Flash Fill works by detecting a pattern in that data and following that pattern while giving you the modified data. At first, when you enter the first name in the first cell, Flash Fill doesn’t recognize the pattern. But when you start typing the first name again in the second cell, Flash Fill recognizes the pattern and shows you the suggestion for splitting first names. Then, just press the ‘Enter’ key.

Generally, the Flash Fill feature is enabled by default. If it does not work in your Excel, after typing the first name in the first cell, you can simply select the second cell and click the ‘Flash Fill’ button from the Data Tools group on the ‘Data’ tab.

Alternatively, you can also press ‘Ctrl’ + ‘E’ to get the same results.

Now, the rest of the cells will be filled out with first names.

Sometimes, you may not see the pattern suggestion in grey, in that case, you can use the fill handle to get the Flash Fill result.

First, type the names manually in two cells and select both these cells. Then, hover your cursor on the lower-right corner of the selection. You will notice that the cursor changes from a small green square (Filler icon) to a plus icon.

Next, double click on that plus icon. This will fill the rest of the cells. At this point, the results are incorrect, you’ll see both first names are repeated again and again. Then, at the lower right corner of the resulting data, you will see a small Auto-Fill icon like it is shown below. Click on this ‘Auto-fill’ icon and select ‘Flash Fill’.

This would fill first names in all the cells:

Remove the Middle Name

You can also use the Flash Fill tool to get rid of the middle name from full name.

For example, let’s say you have the below dataset and you want to get only the first and the last name without the middle name or middle initial.

To get names without a middle name or middle initial, manually type ‘Lord Stark’ in the adjacent cell. Then, in the second adjacent cell, type ‘Daenerys Targaryen’. While you’re typing, Flash Fill will recognize a pattern and it will show you a list of names without middle names (in grey).

If the suggestion is right, press the ‘Enter’ key and Flash Fill will automatically fill the rest of the cells with the names without the middle name.

If you want to get only the middle names without first and last names, enter the middle name in the first two cells and use the Flash Fill tool to get the middle names from all the full names in a column.

This tutorial shows how to separate names while manipulating the text data. These methods can also be helpful when working with other forms of data such as addresses, product names, brand names, etc.