How to Separate Names in Excel

Performing operations on names like search, sort, filter, etc. is usually easier when full names are separated into separate columns.

In fact, separating names is such a frequently performed data processing task, we think Excel should probably introduce a dedicated function for it.

In the meanwhile, however, there is a multitude of ways to separate names in Excel. 

In this tutorial, we are going to show you 5 such methods:

• Using the Text-to-Columns Feature
• Using the Flash Fill feature (Excel 2013 and later versions)
• Using Find and Replace (with wildcard characters)
• Using a formula
• Using Power Query

How to Separate/Split Names in Excel

Let us look at 5 ways to separate names in Excel. To demonstrate each method, we will work with the following list of names:

Notice that all the names contain a middle name too.

As you read through the different methods, you will notice that the middle name is usually the most difficult to extract.

However, we have provided detailed steps to help you get the job done quickly and easily.

Method 1: Using the Text-to-Columns Feature to Separate Names in Excel

This is the easiest and the most commonly used method to separate names in Excel.

The reason it is preferred over most methods is that it is simple, quick, and lets you split names, even if there is a middle name.

Moreover, it works with different naming formats, as long as the names are separated by a common delimiter, like a space or comma.

Here are the steps to separate names using this method:

1. Select the range of cells that you want to work with (cells A2:A7 in our example).
2. From the Data menu, click on ‘Text to Columns’ (under Data Tools).

3. You should now see a ‘Convert text to Columns Wizard’ box.
4. Step 1 of the wizard lets you select how you want your columns separated. You can separate your selected column either by a delimiter or by a fixed width. Since the names are all separated (or delimited) by a space, it makes sense to select the ‘delimited’ option.

5. Click Next.
6. Step 2 lets you select the delimiters by which you want to split your column. The names are separated by spaces, so check the box next to ‘Space’.
7. Sometimes certain names might have more than one consecutive space between them. To make sure that Excel trims all these extra spaces, make sure to keep the box next to ‘Treat consecutive delimiters as one’ checked.

8. At the bottom of the wizard, there’s a preview box where you should now be able to see how your data is going to look after the split.

9. Click Next.
10. Step 3 lets you select the format you want for each of your extracted columns. Since the names are all text, you can simply leave the ‘General’ option selected. 
11. This step also lets you select where you want your separated columns to start from. Since we want to keep the original names and display the separated names in a new column, let us specify the cell reference $B$2 in this field.

12. Click Finish.

You should now find your names separated into three columns, one for each part of the name. You can rename your newly extracted columns as ‘First Name’, ‘Middle Name’, and ‘Last Name’.

Note that this method will work only when you have consistent names data. For example, if you only have first and last name, or first, middle, and last name, then you can use Text to Columns. But if you have a mix of names (with and without middle name or suffix/prefix), then the result from this method would be inconsistent

Also read: Split Text to Rows in Excel

Method 2: Using the Flash Fill Feature to Separate Names in Excel (Version 2013 onwards)

If you’re using Excel 2013 or later versions, the Flash Fill is one feature that is going to save you a lot of time!

This Excel feature recognizes patterns in your data and fills the rest of the column using the same pattern. 

Since names usually have a consistent pattern the flash fill feature can be a big time saver when you want to separate them.

Here are the steps to separate names using this method:

1. Manually split the first name in your names list into three separate columns, as shown below:

2. Click on the second row of the First Name column and press the Flash fill button. You can find the button in the Data tab, under ‘Data Tools’. Alternatively you could use the CTRL+E shortcut to automatically perform the flash fill.

3. Repeat step 2 for the Middle and Last Name columns separately.

You should now find all your first, middle, and last names separately flash filled into three separate columns.

Since the flash fill feature is quite good with recognizing patterns, it’s a great way to separate names, even when your names are in the comma-separated format as shown below:

Unlike the Text to Columns method, the Flash Fill method to split names in Excel would work fine even when you have a mix of names (with and without a middle name). This is because Flash Fill can identify the pattern and can recognize that you’re trying to extract the first word of the name or the last word of the name.

Note: Flash Fill works well in most cases, but make sure to scan the result and ensure everything looks good. There could be some instances where Flash Fill is not able to recognize the pattern and may give you the wrong result.

Also read: Remove Middle Name from Full Name in Excel

Method 3: Using Find and Replace (using wildcard characters)

Excel’s Find and Replace feature is a versatile one since it lets you do a lot more than just find and replace things.

The best thing about it is that it lets you use wildcards to target specific patterns in your data.

A wildcard is a symbol that represents a set of characters. For example, the asterisk symbol (*) is a wildcard that represents any number of characters in a string.

So, if you want to look for all names that start with a ‘B’, you can use ‘B*’ as the search string.

When you combine the asterisk wildcard with the Find and Replace feature, you can extract all characters that follow or precede a delimiter, like a space character or comma.

Unfortunately, this method can only help you extract first and last names. You cannot use it to extract the middle names.

To use the Find and Replace feature to separate names in Excel, follow the steps below:

1. Copy the range of cells containing the list of names and paste them as they are in the two columns (for First Name and Last Name) as shown below:

2. Select the names in the First Name column and click on the Find & Select button from the Home tab. You will find this button in the Editing group. From the dropdown list that appears, select the Replace option. This opens the Find and Replace dialog box. Alternatively, you could press the CTRL+H shortcut to open the dialog box and then select the Replace tab from it.

3. In the input box next to ‘Find what:’, type your search string. For the First Name column, type the search string ‘ *‘, which is a space character followed by the asterisk symbol.
4. Leave the input box next to ‘Replace with:’ blank, as you want to delete everything after the first space character (to extract the first name).

5. Click the ‘Replace All’ button.

6. A message box should appear letting you know how many replacements were made and your selected column should now contain only the first names.

7. Select the names in the Last Name column and repeat steps 3 to 6, with the exception that in step 4, the search string entered in the ‘Find what’ input box should be ‘* ‘ (asterisk symbol followed by a space character). 

8. Here’s how the columns should look at the end of the above steps:

Also read: Switch First and Last Name with Comma in Excel (Flip Names)

Method 4: Using Power Query to Separate Names in Excel

This method can be a little more complex and lengthier compared to the other methods discussed so far, but you will find it helpful in the longer run, especially if you use Power Query as part of your data processing toolkit.

You can simply add the steps discussed in this section to your Power Query data cleaning process and avoid having to repeat it every time you work with names in your data.

If you are not familiar with Power Query, it is a tool that facilitates the extraction and transformation of data in Excel.

In 2016 and later Excel versions, you will find it in your Data tab. In older versions, you need to install it as an add-in.

Power Query includes a data splitting feature that is quite similar to the ‘Text to Columns’ feature discussed in the first part of this tutorial.

Let us see how we can use this feature to separate names in Excel:

Note: To apply Power Query your data needs to be in the form of an Excel Table. You can convert your data to an Excel table by selecting it and navigating to Insert->Table. Alternatively, you can just use the shortcut CTRL+T. When the Create Table dialog box opens, make sure it shows the correct range of cells containing your data and click OK.

1. Select any cell in the Excel table containing your data.
2. From the Data tab, click on ‘From Table’ (under the ‘Get and Transform Data’ group).

3. This will open the Power Query Editor.

4. Click on any cell of your Full Name column and click on the Split Column dropdown (under the Home tab of the Power Query editor).
5. From the dropdown menu that appears, select ‘By Delimiter’.

6. This will open the Split by Column Delimiter dialog box.

7. Under ‘Select or enter delimiter’, keep the Space option selected (since the parts of each name are delimited by spaces).
8. Under ‘Split at’, keep the ‘Each occurrence of the delimiter’ radio button selected.
9. Click OK.
10. This will separate your Full Name column into three parts.
11. Double click on the column headers and change them to the appropriate header names as shown below:

12. Click on the Close & Load button (under the Home tab).

13. You will find a new sheet in your workbook containing the separated name columns.

Also read: How to Generate Random Names in Excel

Method 5: Using a Formula to Separate Names in Excel

The last method involves using formulae to separate names in Excel. There is a different formula to extract each part of the name.

So this method is probably the most complex one. That’s why we included it in the end.

Oftentimes you might need to use extracted parts of the names for further processing. So, knowing the formula to extract each part of the name might come in handy in such cases.

Let us look at the formulae to extract the first, last, and middle names one by one:

Formula to Extract First Name

The formula to extract the first name from a given name string is as follows:

=LEFT(name_string,SEARCH(“ “,name_string)-1)

In the above formula, you just need to replace the name_string variable with a reference to the cell containing the name.

So in our example, if you want the  first names to appear in column B, you can simply type the following formula in cell B2:

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

Then press the return key and copy the formula down to the rest of the cells in the column. Here’s the result you should get:

Explanation of the Formula

The above formula uses the SEARCH function to find the location of the first space character in the name.

It then uses the LEFT function to extract all the characters that come before the space.

So for our first name, the SEARCH function returns the index 4 (position of the first space character – after Ted).

The LEFT function then extracts all 3 (4 – 1 = 3) characters before that space (The characters “Ted”) and displays it.

Formula to Extract Last Name

The formula to extract the last name from a given name string is as follows:

=RIGHT(name_string,LEN(name_string)-SEARCH("@",SUBSTITUTE(name_string," ","@",LEN(name_string)-LEN(SUBSTITUTE(name_string," ","")))))

So in our example, if you want the last names to appear in column D, you can simply type the following formula in cell D2:

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

Then press the return key and copy the formula down to the rest of the cells in the column. Here’s the result you should get:

Explanation of the Formula

The SEARCH function basically returns the position of the first occurrence of a character.

However, there are two space characters in the full name. So, to find the last space character, we need to improvise.

For this, we use the LEN(A2) function to find the total length of the full name and LEN(SUBSTITUTE(A2,” “,””)) to find the length of the full name without the space characters.

We then subtract the second value from the first one to get the total number of spaces in the name string.

We are only interested in the last space character because that’s what will lead us to the last name.

To mark the last space character, we use the SUBSTITUTE function, which finds the last occurrence of the space character and replaces it with an ‘@’ symbol.

You can use any symbol in its place, as long as it’s not a symbol that you will typically find in names.

Now that the last space character has been marked with an ‘@’ symbol,all that’s left to do is find this symbol and extract all the characters to the right of it.

This will give us the last name. To extract all the characters to the right of the ‘@’ symbol, we used the RIGHT function.

So, for our first name, the SUBSTITUTE function replaces the 2nd (19 – 17 = 2) space character with an ‘@’ symbol, so that the name now becomes “Ted Raphael@Johnson”. The RIGHT function then extracts all characters to the right of the ‘@’ symbol, i.e. “Johnson” and displays it.

Formula to Extract Middle Name

The formula to extract the middle name from a given name string is as follows:

=MID(name_string,SEARCH(" ", name_string)+1,SEARCH(" ",SUBSTITUTE(name_string," ","@",1))-SEARCH(" ", name_string))

So in our example, if you want the middle names to appear in column C, you can simply type the following formula in cell C2:

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

Then press the return key and copy the formula down to the rest of the cells in the column.

Here’s the result you should get:

Explanation of the Formula

The MID function can help extract characters from the middle of any string if you can provide it with the starting position and the number of characters that you want to extract from that position.

The SEARCH(“ “,A2)+1 easily gives the starting position for the middle name (one position after the first space).

However, finding out how many characters to extract can be a little tricky.

You can use the SUBSTITUTE function to replace the last space character with an ‘@’ symbol.

You can then find the difference between positions of the first and last spaces to get the total number of characters in the middle name. 

=SUBSTITUTE(A2," ","@",1))-SEARCH(" ",A2)

This gives the number of characters you want to extract. 

Use that in the third parameter of the MID function and you get the middle name extracted and displayed!

In this tutorial, we showed you 5 ways to separate names in Excel, including cases where you have a first, middle, and last name.

We hope the tutorial was helpful for you.

Other Excel tutorials you may also like:

• How to Merge First and Last Name in Excel (2 Easy Ways)
• Extract Last Name in Excel – 4 Easy Ways!
• How to Extract Text After Space Character in Excel?
• How to Extract Number from Text in Excel (Beginning, End, or Middle)
• How to Remove a Specific Character from a String in Excel
• How to Remove Text after a Specific Character in Excel (3 Easy Methods)
• How to Add Text to the Beginning or End of all Cells in Excel
• How to Separate Address in Excel?
• How to Combine Two Columns in Excel (with Space/Comma)
• Opposite of Concatenate in Excel (Reverse Concatenate)