How to separate First and Last name in Excel [easy methods]

August 29, 2021

It's very common to get a database that contains a column with full names, separated either by space, comma, or a mix of symbols. That is why it's important to understand how you can easily separate first and last name in Excel. This article will save you a lot of time, as it shows you how to achieve this using multiple methods: Text to Columns, Flash Fill, formulas, and the Find and Replace tool.

Before we begin, I encourage you to download the Excel file that I've used when writing this article.

Separate First and Last name in Excel using Text to Columns feature

When you have a column of names with the same pattern (for example, only first and last name), the easiest option to split them into separate columns is to use the Text to Columns feature. This works in 5 easy steps:

1. Select the column containing the full names.

2. Navigate to the Data tab and select Text to Columns from the Data Tools group.

Separate First and Last name in Excel using Text to Columns

3. When the Convert Text to Columns Wizard box opens, choose Delimited and click Next.

Convert Text to Columns Wizard

4. On the next step, select Space as delimiters and click Next.

Select Space as delimiter and click Next

If your data set has more delimiters, you can select multiple options. For example, if your names are formatted as John, Smith then you can select comma and space as delimiters and tick the Treat consecutive delimiters as one checkbox.

The Data preview section allows you to see how your names will be split. If everything looks fine, click Next. Otherwise, adjust as necessary.

5. The final step is to select the Column data format and the Destination. When you are satisfied with the result, press Finish.

Select the Column data format and the Destination

And voilà. Now you know how to separate names in Excel using the Text to Columns feature.

The first and last name are separated into different columns

How to split First and Last name in Excel using a formula

The Text to Columns solution is great when you have static data. But if you plan on updating the names frequently, then a formula solution is the better way to go. This allows you to split First and Last names dynamically.

Split first and last name for space-separated data (names with spaces)

Splitting the first and last names involves writing two formulas: one to get the first name, the other to get the last name. These formulas work with the first name and last name stored in one column and separated by a single space character. If you are looking for a solution for comma-separated names, skip to the next section.

Formula to separate the first name

The formula to get the first name uses two functions: LEFT and SEARCH.

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

Formula to separate first name in Excel

Now, let me explain how this formula works.

The LEFT function returns the specified number of characters from the start of the string. You supply the string, in our case, the cell containing the full name, and the number of characters that you want to extract.

For this formula to work on any name, we need to supply the number of characters in the LEFT function dynamically. So, we use the SEARCH function, which returns the number at which the character or string is first found, reading the date from left to right. In our example, we supply the space as the character, and the SEARCH function will return 9. We then subtract 1 because we do not want to include the space in our result, and we end up with "Roseanna."

Formula to separate the last name

The formula to get the first name uses three functions: RIGHT, SEARCH, and LEN.

=RIGHT(B9, LEN(B9)-SEARCH(" ", B9))

Formula to separate last name in Excel

Getting the Last name is a bit trickier because we actually need to start from the space character and extract the last characters.

Because SEARCH only reads data from left to the right, we need to count the total number of characters in our cell and then subtract the result returned by the SEARCH function. To achieve this, you need to add the LEN function, which does exactly that: it returns the number of characters in a text string.

Separate first and last name for comma-separated data

If your series contains data separated by a comma and a space, you can adapt the formula to extract the First name from the previous example by subtracting 2 instead of 1.

Formula to separate first name in Excel for comma and space separated names

=LEFT(B9, SEARCH(" ", B9)-2)

The formula to get the Last name remains unchanged.

=RIGHT(B9, LEN(B9)-SEARCH(" ", B9))

Split first, last, and middle name

The most challenging part is when you have a complex full name that includes the middle name or a middle name initial, such as "Rock David Giorgini" or "Mel B. Gregoletti."

Getting the First name is always easy, and you can use the same formula as in the previous examples.

How to split first name in Excel

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

Now, the trickiest part is to get the middle name or the middle name initial. That is because there are multiple spaces, and you need to identify exactly which part of the string you want to extract. For this, we use the MID function, along with multiple SEARCH formulas.

How to split middle name or middle name initial in Excel

MID returns the characters from the middle of a text string by providing a starting position and the length of the string you want to extract. The formula looks like this:

=MID(B9, SEARCH(" ", B9)+1, SEARCH(" ", B9, SEARCH(" ", B9)+1)-SEARCH(" ", B9)-1)

This formula determines the position of the first space and adds 1 to start from the position immediately after it. This is the start_num argument of the MID function.

Then, it uses three nested SEARCH functions to determine how many characters are in the Middle name. To do this, the formula identifies the position of the first space and subtracts the corresponding number from the position of the second space. You need to subtract 1 from the result because you want to remove the trailing space as well.

The final step is to insert the result as the num_chars argument of the MID function, and you are good to go.

How to split last name in Excel

We can retrieve the Last name using the RIGHT, LEN, and SEARCH functions similar to the previous examples. However, since our names are more complex, we need to add an extra SEARCH function in our formula. Here's how the formula looks like:

=RIGHT(B9, LEN(B9)-SEARCH(" ", B9, SEARCH(" ", B9, 1)+1))

The formula determines the position of the second space by using two nested SEARCH functions., then subtracts the position of the second space from the total string length so that it determines the length of the last name. Then, it uses this number inside the RIGHT function, telling Excel exactly how many characters to return from the end of the string.

Separate names using Find and Replace

Find and Replace can be very a very powerful tool to use, but keep in mind that it does just that: it replaces your data. So, make sure you create a copy of your data before using this method. Otherwise, you risk altering your original data set.

The first step I always take is to copy all data into a new column. I have created two working columns called First name and Last name, and I have copied all the names into both.

Preparing data to separate names using Find and Replace

How to split the First name using Find and Replace

A few simple steps are needed to separate First and Last name in Excel using the Find and Replace tool.

1. Select all data from the First name column.

Select all data from the First name column.

2. With the data in column First name selected, navigate to Home > Editing > Find & Select and choose Replace. Alternatively, you can use the keyboard shortcut CTRL + H. This will open the Find and Replace dialog box.

Find and Replace menu

3. In the Find and Replace dialog box, enter the following:

  • Find what: " *" (that is one space and the asterisk symbol).
  • Replace with: leave blank
Find and Replace options for split First names

4. Press Replace All, and you are done. Excel will remove all the Last names from the selected data.

First name split using Find and Replace option.

How to split the Last name using Find and Replace

Retrieving the Last name is just as easy.

1. Select all data from the Last name column.

Select all data from the Last name column.

2. With the data in column Last name selected, navigate to Home > Editing > Find & Select and choose Replace or press CTRL + H.

3. In the Find and Replace dialog box, enter the following:

  • Find what: "* " (that is the asterisk symbol and one space).
  • Replace with: leave blank
Find and Replace options for split Last names

4. Press Replace All, and you are done. Excel will remove all the First names from the selected data.

Last name split using Find and Replace option.

How to separate names in Excel using Flash Fill

Quick note: Before we begin, I'd like to point out that this tool is available only in Excel 2013, 2016, 2019, and Excel for Microsoft 365. If you are using an older version of Excel, try one of the methods described above.

Flash Fill is good for quickly finding patterns. Very good, actually. To use Flash Fill to split the First or Last name, follow these simple steps:

  1. Add a column next to the column with your original data.
  2. In the first cell, write the part of the name that you want to extract. In this example, it is the First name.
  3. Move to the next cell and start typing the second First name. When Excel finds a pattern, it will automatically show a drop-down list with matching values.
  4. If you are satisfied with what you see, simply press the Enter key.
Separate names in Excel using Flash Fill

If you use an Excel version that supports Flash Fill, but no suggestions appear, make sure Flash Fill is enabled. To check this, go to File > Options, click on Advanced, and make sure that the Automatically Flash Fill box is selected under Editing options. If you are unsure of your Excel version, you can read the article I wrote on how to check what version of Excel you have installed.

If you want to learn more about Flash Fill, you can read this article from Microsoft's website.

Reverse First and Last name with Flash Fill

Flash Fill can work wonders on large data sets and provides a lot of flexibility. For example, you can change the order of names from "First name, Last name" to "Last name, First name" just as easily.

In the adjacent cell, manually type the new order. Then, move to the cell below and start writing the second name. When the Flash Fill suggestion appears, simply press Enter, and you are done.

Reverse First and Last name with Flash Fill

And this is not all. You can also remove middle names or middle name initials, change your data structure, remove commas, or convert the names to email addresses just as easily.

What to do next?

Now that you've learned how to separate names in Excel, I strongly encourage you to continue learning about other useful functions. I have prepared a list of articles that includes common scenarios that you can encounter at your job or useful functions that every Excel user should master:

If you have any questions, please leave a comment, and I will get back to you as soon as possible. I reply to all the comments that I get.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Essential Functions

Excel Topics

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2021 ExcelExplained.com