How To Separate Names In Microsoft Excel

When we copy-paste names to the Excel grid, the full name is often pasted on a single column. It can be a hassle sorting data into separate columns, especially for bigger files. As this problem is quite common, Excel has built-in features to separate texts.

Excel offers built-in features to separate first, middle and last names. Keep reading this article to separate names in Excel depending on the separator you’ve used to separate the words.

How to Separate Names in Excel?

If you wish to separate names into separate columns, you can use the Text to Column tool to separate each text. You can also use formulas using functions like LEFT/RIGHT, LEN, SEARCH, and SUBSTITUTE to separate first middle and last names. Then, you can use Flash Fill to fill in the rest of the cells. 

Similarly, you can use the Power Query to separate full names if you’re an advanced user.

Use Text to Columns

The Text to Columns tool in Excel changes the alignment of your cell value and can also be used to add space between your names. When using this tool, your information will be separated into two columns.

1. Launch Excel.
2. Select a cell from the table
3. Head to the Data tab, then locate and select Text to Columns.
   
4. In Step 1 of 3 of the Convert Text to Columns Wizard, select the box next to Delimited.
   
5. Select Next.
6. In Step 2 of 3, check the box next to Space > Next. If some other separator separates your text, select the box next to the name of the separator > Next.
   
7. In step 3 or 3, select the box next to General.
   
8. Next to the Destination section, enter the column you wish to enter the separated data.
9. Choose Finish.

Use Library Functions

You can use functions readily available in Excel to extract first and last names, separated by a space or comma. Additionally, you can also extract middle names that are separated by a space.

In the given formulas, in place of the “cell”, enter the location of the cell with the full name.

Separated by Space

If a space separates the full names, you can use these steps to extract the first, last and middle names into different columns.

First Name

1. On your workbook, select an empty cell.
2. Enter the formula in this format to extract the first name:
   =LEFT(cell, SEARCH(" ", cell) - 1)
   

3. Use Flash Fill to fill in the rest of the column.

Middle Name

1. Open your workbook and select a new cell.
2. Enter your formula in the following format:
   =RIGHT(cell, LEN(cell) - SEARCH("#", SUBSTITUTE(cell," ", "#", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))
   

3. Use Flash Fill to fill in the remaining columns.

Last Name

1. On the Excel grid, select a new cell.
2. Enter the formula in the following format:
   =RIGHT(cell, LEN(cell) - SEARCH(" ", cell))
   

3. Use Flash Fill to extract last names from the full name.

Separated by Comma

You will have to use a different formula to extract the first and last names if commas separate your full names.

First Name

1. On your Excel grid, select a new cell.
2. Use this format to enter the formula to separate names by commas:
   =LEFT(cell, SEARCH(",", cell) - 1)
   

3. Use Flash Fill in the rest of the columns.

Last Name

1. Select an empty cell from your workbook.
2. Enter the formula in this format to extract the last names:
   =RIGHT(cell, LEN(cell) - SEARCH(",", cell))
   

3. Use Flash Fill to fill the remaining columns.

Use Power Query

If you’re using the 2016 version of Excel or later, you can use Power Query to split the full names into different columns. As using Power Query is a bit complicated, we advise only advanced Excel users to use this method.

Before opening your data in Power Query, convert your data to a table. Select your data from the grid, then head to the Insert tab, then select Table. After you’ve converted your data to a table, you can load your table into the Power Query.

1. On your workbook, select a cell from the table.
2. Head to the Data tab.
3. Select From Table in the Get & Transform section.
   
4. Select a cell from the column with full names 
5. From the Home tab, select Split Columns > By Delimiters.
   
6. Specify your separator in the window, then Click OK.
   
7. From the top-left corner of the Power Query, select Close and Load.

Use Third Party Add-ins

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.

How to Automate Separating Names by Creating a Macro?

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. When you record a Macro, Excel will generate a code following your keystrokes that you can run to replicate the action.

After you record your Macro from the developer tab, you can continue to separate the full names from the grid following the above-mentioned methods. After you record your Macro, you can run the Macro the next time you need to separate names.

.