How to separate First and Last name in Excel [easy methods]
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 spreadsheet that I’ve used when writing this article.
How to 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 wizard. 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.
3. When the Convert Text to Columns Wizard box opens, choose Delimited and click Next.
4. On the next step, select Space in the delimiters section 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. Keep in mind that any existing data in the destination cells will be replaced.
And voilà. Now you know how to separate names in Excel using the Text to Columns feature.
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)
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))
Getting the Last name is a bit trickier because we actually need to start from the space character and extract the last characters from the right side.
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. The next formula allows you to remove space as well.
=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 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.
=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.
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.
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.
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.
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.
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
4. Press Replace All, and you are done. Excel will remove all the Last names from the selected data.
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.
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
4. Press Replace All, and you are done. Excel will remove all the First names from the selected data.
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:
- Add a column next to the column with your original data.
- In the first cell, write the part of the name that you want to extract. In this example, it is the First name.
- 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.
- If you are satisfied with what you see, simply press the Enter key.
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.
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:
- learn how to count cells between two dates
- use complex IF statements with AND, OR, and NOT conditions
- learn how to fix common Excel errors like #NULL!, #REF!, or #N/A
- learn how to sum sales by year using SUMIFS
- advanced tutorial on how to use VLOOKUP function
- advanced tutorial on how to use INDEX and MATCH functions
I hope you’ve found this article helpful. 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.
