How to Separate First and Last Name in Excel

Large Microsoft Excel sets of data may require you to split people’s first and last names in your data tables. In this article, you can learn how to do just that. Slice and dice your data with ease by following this Excel tutorial for beginners and intermediate users.

Let’s say you’re working on creating a personalized email template. Each message needs to contain the name of the recipient — if you want this to be more friendly, you’d want to use first names only. Luckily, Excel gives you the option to split first and last names into separate cells.

Having a lot of data stuffed into just one cell will often present problems. In this case, it’s personalization. Sometimes, too much in a field may hinder your ability to sort and filter your data, as well. You can use the methods below to split more kinds of data off to their own cells, such as addresses, birthdays, and so on.

Split first and last names in Excel

In the guides below, you can learn how to separate first and last names in Excel. For the purposes of this guide, we’ll be using the latest Microsoft Excel 2019, however, the steps work on all versions of the software.

Do you want free access to Excel? Try Excel Online, the ever-growing online version of the software, provided by Microsoft themselves. If you need further assistance, don’t hesitate to contact us.

Method 1. Use the Text to Columns function

Text to Columns in Excel allows you to split text values into separate cells in a row. You can use this to separate a first name and last name from the full name.

1. Open the Excel document containing your names, or create a new one and set up your data. Make sure that your cells are set up in a similar way to our example below:
   
   
2. Use your cursor to select all of the names in the full name column. In our example, we’ll select A2:A10.
   
   
3. Switch to the Data tab in your ribbon interface. This should be located between the Formulas tab and the Review tab.
   
   
4. Click on the Text to Columns button, found in the Data Tools section. (See image below)
   
   
5. A pop-up window will appear and launch the Convert Text to Columns Wizard. This can be broken down into 3 steps, each of which requires you to make adjustments.
6. In Step 1, select Delimited in the Original data type section. This will allow you to use space as the separator between first and last names. Click on Next to proceed.
   
   
7. In Step 2, ensure that you uncheck all of the Delimiters first, and then check Space only. Once again, click Next to proceed.
   
   
8. Make sure to select the field for your output in the Destination section. For our example, we selected the first cell in the First name column, B2. Click Finish to wrap up the process.
   
   

As seen below, we were successfully able to separate first and last names from one single cell into 2 separate cells. This opens up a plethora of options for personalized emails, data analysis, and more.

However, there are some things you need to keep in mind when working with this method. Make note of the following warnings:

• The data in your cells are static. When you edit the full name, the first and last name will not adjust according to your changes. You’ll need to manually edit them to reflect the changes.
• If you don’t specify a destination cell, Text to Column will overwrite the current column. This erases the full name fields, replacing them with separated first and last names.
• Do you want the first or last name only? In Step 3, select the column you want to skip, and then select the Do not import column (skip) option.

The Text to Columns method is best suited when working with consistent data. For example, when all names have first and last names only, or all names have a middle name. Mixing and matching will lead to faulty results — if your data is inconsistent, work with formulas instead.

Method 2. Formulas

A different approach to separating the first and last names in Excel is by using formulas. For this, you’ll need 3 different formulas, depending on whether or not you have middle names in your list.

The strength of using formulas is its dynamic way of extracting data. If you change the full name, the change will automatically be reflected in the separated first, middle, and last name cells as well.

While this method is more advanced, we’re here to help. Let’s get started in breaking down the 3 formulas you’ll be using to break full names down.

1. Formula to get the first name

To separate the first name from the full name, you’ll need to use the following formula. Simply select any empty cell as the destination, and then insert the following: =LEFT(A2,SEARCH(" ",A2)-1)

This formula is assuming that the full name is contained in the A2 cell. If yours is in a different position, make sure to change it in the formula. For example. If your first full name field is D7, the formula would change to: =LEFT(D7,SEARCH(" ",D7)-1)

After running the formula once, you can easily repeat it by positioning your cursor to the bottom-right corner of the cell and dragging it down. Doing so will automatically adjust the formula for the other full name fields and separate the first name:

Imgur link

2. Formula to get the middle name

Let’s say that some of the full name entries in your data set have a middle name. In this case, you might want to extract these in their own column. The following formula will let you do just that: =MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",SUBSTITUTE(A2," ","@",1))-SEARCH(" ",A2))

Again, this formula is assuming that the full name is contained in the A2 cell. If yours isn’t, make sure to adjust it in the formula itself. You can also use the drag handle to copy and automatically adjust the formula to the other full name cells, and populate the middle name column.

It’s best to use consistent data — if some of the entries have middle names, while some don’t, the missing middle names will be replaced with a #VALUE! Error. You’ll need to get rid of these manually.

3. Formula to get the last name

Finally, the last formula is used to extract the last name from a full name. Simply select a destination cell, and then insert the following: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Just like the previous formulas, this one also assumes that the full name is contained in the A2 cell. If yours is different, make sure to adjust it in the formula itself. Use the drag handle to copy and automatically adjust the formula to the other full name cells, and fill up the last name column.

In the case that you also have middle names, use the following formula: =RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Again, replace any instances of the A2 cell if your full name is located in a different field.

Method 3. Find and replace

Using wildcard characters, you’re able to separate first and last names with Excel’s Find and replace function. This is a bit of an odd one, but it works, and it might be quicker than the other options. Let’s see how you can break up full names using it

1. Get the first name using Find & replace

1. Select all the cells holding full names, and then right-click on any of them and choose Copy from the context menu. Alternatively, use the Ctrl + C keyboard shortcut.
   
   
2. Select the destination cell, and then right-click and choose Paste from the context menu. You can also use the Ctrl + V keyboard shortcut. You should have an exact copy of the full names.
   
   
3. Keep the second column selected, and stay on the Home tab. Click on the Find & Select button from the Editing section on the far right.
   
   
4. From the context menu, select Replace. A new pop-up window will appear.
   
   
5. Type a space ( ) character, and then an asterisk (*) into the Find what field. Leave the other field blank, and then click Replace all.
   
   
6. Done!

2. Get the last name using Find & replace

1. Select all the cells holding full names, and then right-click on any of them and choose Copy from the context menu. Alternatively, use the Ctrl + C keyboard shortcut.
   
   
2. Select the destination cell, and then right-click and choose Paste from the context menu. You can also use the Ctrl + V keyboard shortcut. You should have an exact copy of the full names.
   
   
3. Keep the second column selected, and stay on the Home tab. Click on the Find & Select button from the Editing section on the far right.
   
   
4. From the context menu, select Replace. A new pop-up window will appear.
   
   
5. Type an asterisk (*), and then space ( ) character into the Find what field. Leave the other field blank, and then click Replace all.
   
   
6. Done!

Final thoughts

We hope this article was able to teach you how to separate full names into first and last names in Microsoft Excel. Use the methods above to create rich, personalized data sheets with ease. You can even mix and match different techniques to speed up your workflow!