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.
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:
|LEFT||Extracts a specified number of characters from the left.|
|RIGHT||Extracts a specified number of characters from the right.|
|MID||Extracts a specified number of characters from the set position.|
|SEARCH||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.
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.