How To Separate Names In Excel

For longer name lists, use automation tools like Text to Columns and Power Query to separate names in Excel.

Additionally, you can use the LEFT, RIGHT, SEARCH, and MID functions to construct a formula to separate names in Excel.

Note: We’ve assumed that your names are separated by space in between. If you have a different delimiter (e.g., a comma), simply swap the space character with your delimiter.

Table of Contents

Use Text to Columns to Separate Names in Excel

The Text to Columns tool is an extremely handy tool for data parsing. Using a delimiter, you can use this utility to divide names into different columns.

This tool will divide any data between the set symbols into separate columns. I recommend you use this method if there are no middle names in your name list.

Select your range.
Head to the Data tab and select Text to Columns from Data Tools.
In Step 1 of 3, choose Delimited > Next.
In Step 2 of 3, select Space > Next.
In Step 3 of 3, click on the box next to General.
Specify the location you wish to place your columns next to Destination.
Click Finish.

Use Library Functions to Separate Names in Excel

You can also nest the LEFT, RIGHT, SEARCH, and MID functions to create a formula to separate names in Excel.

If you’re not familiar with these functions, here are their arguments and, a little bit about what they do:

Function	Format	Description
LEFT	`=LEFT(text, [num_chars])`	Extracts a specified number of characters from the left.
RIGHT	`=RIGHT(text, [num_chars])`	Extracts a specified number of characters from the right.
MID	`=MID(text, start_num, num_chars)`	Extracts a specified number of characters from the set position.
SEARCH	`=SEARCH(find_text, within_text, [start_num])`	Returns the number of characters on the left of the specified character.

Here, we have a spreadsheet with full names in column A. We will use three formulas to extract the first, middle, and last names in columns B, C, and D.

Separate First Name

On your workbook, select an empty cell.
Enter the formula in this format to extract the first name:

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

Use Flash Fill to apply the formula to the entire column.

Separate Middle Name Initial

Open your workbook and select a new cell.
As not all names have a middle name, we’ll be nesting our formula inside IFERROR:

=IFERROR(MID(A2,SEARCH(".",A2)-1,2), " ")

Use Flash Fill to fill in the remaining columns.

Note: If you choose not to use the IFERROR function, names with no middle initials will return the #VALUE! error.

Separate Last Name

On the Excel grid, select a new cell.
Enter the formula in the following format:

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

Use the fill handle to extract last names from the full name.

We used the IF function because there are two instances. Earlier, we used the IFERROR function to return space if there’s no middle initial. If the area has space, we will be using the first formula to extract the last name.

Otherwise, we will be removing 3 characters from the left when extracting the last name. This will subtract the middle initial and space when returning the last name.

Use Power Query to Separate Names in Excel

Power Query is a powerful automation utility in MS Excel. If you’re using the 2016 version of Excel or later, you can use Power Query to separate names in Excel.

On your workbook, select a cell from the table.
Head to the Data tab.
Select From Table in the Get & Transform section.
If prompted, click OK on the Create Table pop-up.
Select a cell from the column with full names
From the Home tab, select Split Columns > By Delimiters.
Specify your separator in the window.
If your data has,

Only First and Last names: Choose Each occurrence of the delimiter
First, Middle, and Last Name:
a. Select Right-most delimiter.

b. Repeat steps 6 and 7 then choose Left-most delimiter.

Click OK.
From the top-left corner of the Power Query, select Close and Load.

Assign a Macro to Separate Names in Excel

If you regularly have to separate first and last names, you can create a macro to automate this task.

In Excel, Macros register your keystrokes to create a Visual Basic code. Excel will create a code using these movements and replicate them when you run your macro.

If you haven’t already, enable the Developer tab.
From the Developer tab, select Record Macro.
Give your macro a name, and assign a Shortcut key.
Use Text to Columns to separate names.
From the Code section, click on Stop Recording.
To run the macro, simply use the assigned key combination as a shortcut.

Use Third-Party Add-ins to Separate Names in Excel

You can also use third-party add-ins to separate names in Excel. Using third-party add-ins is more convenient than using the Text to Column tool or the Excel functions.

However, you must be aware that third-party add-ins can put your device at risk of malware.