How-To-Separate-Names-In-Excel-Someka-Blog-Featuted-Image

How to Separate Names in Excel? Split First and Last Names

Excel Tips
Updated on October 31, 2024

You’re working with a list of clients, students, or employees, and want to split full names into first names, middle names, and last names? Here’s a complete guide on how to separate names in excel.

Table Of Content

1. How to Separate Names in Excel?

#Method1: Using Formulas
#Method2: Text to Columns
#Method3: Flash Fill
#Method4: Find and Replace

2. How to Split Middle Names in Excel?

3. How do I separate Mr from a name in Excel?

4. Conclusion

Here are some easy methods to separate names in Excel.

1. How to Separate Names in Excel?

Suppose that you have a long list of people and you need to split these names into first name and last names. Basically, this will need to separate a text in a single cell into two or more cells.

Basically, there are four main ways to split names in Excel:

Formulas
Text to column
Flash Fill
Find and Replace

Let’s go deeper into each of these methods with step-by-step instructions in addition to pros and cons.

Method 1: Using Formulas

Firstly, the Excel formulas can be effective for separating names in a bulk list.

You’ll mostly need Excel TEXT functions here, which will include a combination of the SEARCH, LEN, LEFT, and RIGHT functions.

Splitting the First Name Formula:

First, we’ll separate the first names of our list.

As our first name in the list is on the cell B3, our formula to split first names will look like this:

=LEFT(B3, SEARCH(” “, B3) – 1)

The below image show how we split the first names:

But, what’s this formula is talking about?

We do not want to just give the formula on how to separate names in Excel. Instead, we want that our readers actually understand the main concept of our formulas so that they can create themself with changing conditions.

Simply, this formula finds the position of the first space character and returns all characters to the left of it.

Well, we’re first searching for the first space in our full name. This SEARCH() formula will give us the order of the space character as a number. Then we decrease this number by 1. Now we know how many characters we have in the first name. Then, with a simple LEFT() function, we command to bring the first x numbers from the left side of the text.

*EVALUATION OF THE FORMULA: For example, for Lionel Messi, the SEARCH(" ",B3) formula will bring us 7 as value. Because the space is the 7th character in this text. Then, we subtract one from that and find out that the first name has 6 characters. So, we bring the 6 first characters from the left side of our full name.

That’s all.

Separate Last Name in Excel

Secondly, we’ll now separate the last names. This will be a little bit more tricky with adding LEN() function and RIGHT() function to the equation.

First, here’s our formula:

=RIGHT(B3; LEN(B3) – SEARCH(” “;B3))

So, this formula calculates the length of the full name, subtracts the position of the first space, and returns the remaining characters.

Again here the main logic stand upon finding the position of the space character.

*EVALUATION OF THE FORMULA: Again, let go through the Lionel Messi example. We have first find the total length of "Lionel Messi" text with LEN() function, which is 12. And then we try to find out the position of the space character with the SEARCH(" ",B3) formula, which brings us 7 as value. Now we know that the Last Name has 12-7=5 characters. And, we command Excel to bring us the 5 character from the right side with RIGHT formula.

Method 2: Text to Columns

Secondly, you can directly use Text to Columns feature to separate your texts into separate columns. So, here’s our second method on how to separate names in Excel.

Step 1: Select your column

Be sure that you have selected all the column you want to split.

Step 2: Go to Text to Column

You can find Text To Column feature under the data tools of data tab. So go to Data > Data Tools > Text to Column on the ribbon.

Step 3: Choose Delimited and click on next

You have two options here: 1. Delimited, 2. Fixed width.

As the first names and last names vary in terms of width, here we’ll continue with Delimited option.

Step 4: Select your delimiter to separate names in Excel

Excel lets you to choose your delimiter while splitting your texts. You can either choose from one of the most common ones like tab, semicolon, comma, space, or just write a custom one to separate your texts from.

So, in our name separation question, we’ll select Space as our delimiter.

Step 5: Define data format and destination

Lastly, we’ll define the data format our split texts and also the destination.

*NOTE: The destination will com as the first cell of your list. So, please do not forget to update it as the adjacent cell. Otherwise, your new columns will overwrite your original list.

Step 6: Click on Finish

After clicking on the Finish button, your name list will turn into First Name and Last Name table.

Pros and Cons of Text to Column to separate names in excel

Text to Column is very easy and handy feature to split text. Additionally, it is flexible so you can easily customize your delimiter, your data format or destination. These are the main pros.

However, if you have a dynamic list, then you’ll have to repeat this each time you add a new item on your list. In this case, the Formula method will be more suitable.

What is Text To Column Shortcut in Excel?

If you’re one of the non-mouse user, you’ll probably ask the shortcut for this feature.

The shortcut for Text to Column is Alt + A + E.

How to separate text in Excel into rows?

If you’re looking to split text into rows, you can use the Text to Columns feature to first separate the text into columns, and then transpose the columns into rows using the Paste Special feature with the Transpose option.

So, you can open the Paste Special window with CTRL+1 shortcut and check the the Transpose box before clicking on enter.

Method 3: Flash Fill

Flash Fill is an intuitive feature that automatically fills your data based on a pattern it detects. In other words, Flash Fill automatically fills a cell series according to the data you provide.

So, If you split the first few names, then Excel can understand that you want to get the First Names from the full names list. This is amazing, isn’t it?

So, in the below example, first we write Lionel to the first row and then in the second it was enough to enter a few letters from Cristiano, and Excel has recognized the pattern and suggested the remaining names. If the suggestion is Ok for you, just click on Enter.

This feature is really amazing. Introduced with Excel 2013, this feature is available for this and later versions.

If you’re using an compatible Excel version and still cannot get Flash Fill suggestions, check whether your Excel settings has turn off this feature or not.

Go to File > Options > Advanced and make sure you have checked Automatically Flash Fill. Then, you can easily separate names in Excel with this feature.

One of the most important cons with this feature is working smoothly only with vertical data. If you have a horizontal data set, it will hard to catch your Flash Fill suggestion.

Method 4: Find and Replace

Another classic but handy method to separate names is using find and replace option to split names in Excel.

Copy the names in the full name column
Paste it to the First Names column
Press CTRL + H to open find and replace wizard
On the Find What section enter space and asterisk ( *)
Click on Replace All

Basically, we have used an wildcard with asterisk here. Simply, we find all texts followed by a space and the replaced them with nothing. This remove all the middle and last names.

Finally, to split the last names, you can use an asterisk followed by a space (* ) and replace them with again nothing.

2. How to Split Middle Names in Excel?

Up to now, we have studied with First Name-Last Name configuration. But what if some of the names includes Middle Names?

So, we can use any of the above methods with minor adjustments.

With the formula method, do not forget that the SEARCH(” “;A1) formula looks for the first space. But in case of middle names, we have at least two spaces in the text.

So, we will need a slightly more complex formula.

Splitting the First Names

Finding the first names will not change. Again use the below formula:

= LEFT(B3;SEARCH(” “;B3)-1)

Please check the below image:

Splitting the Middle Names

Here’s the tricky part. First of all, we’ll use a MID() and SEARCH() functions to extract the text between first name and last name.

The MID() function extracts the middle text whin in a longer text, in which you define the text reference, and starting and ending numbers. Our target is here to find out these starting and ending points, which are basically the first and the single spaces.

= MID(B3; SEARCH(” “;B3) + 1; SEARCH(” “;B3; SEARCH(” “;B3) + 1) – SEARCH(” “;B3) -1)

But as you see in the below image, we have a #VALUE! error for Zlatan Ibrahimović. Because Excel cannot find the second space and gives error.

So, you want to separate names in Excel with a list combining both two and three-name people, you should use an IFERROR function to get rid of this error:

Our updated formula is below:

= IFERROR( MID(B3; SEARCH(” “;B3) + 1; SEARCH(” “;B3; SEARCH(” “;B3) + 1) – SEARCH(” “;B3) -1) ; “”)

Splitting the Last Name

We have learned how to find the second space above. Now all we have to do is combining this with RIGHT() function. But this time, we’ll not give a space as IFERROR value, we’ll write another RIGHT formula:

=IFERROR(RIGHT(B3; LEN(B3) – SEARCH(” “;B3; SEARCH(” “; B3) + 1));RIGHT(B3;LEN(B3)-SEARCH(” “;B3)+1))

Here’s the perfect result:

The formula method might seem a little bit complicated, because it needs some proficiency in Excel functions. But, please keep in mind if your working with dynamic data, the formula method is the best solution.

Other Methods to Separate Middle Names in Excel

You can easily use Text To Column Feature to separate names in Excel including the middle names. Flash Fill feature will also work perfectly. Lastly, you can also use the Find and Replace feature, but you’ll need again more complex wildcards.

3. How do I separate Mr from a name in Excel?

If you’re looking for a specific text split, you can again do this with the above features.

For example, you have Mr. or Mrs. or Miss in your name list. And you need the clean name list without these titles.

First, you can use Find and replace for each of them one-by-one. Thus, you’ll use title followed by the asterisk (Mr. *) as wild card here.

Alternatively, you can use a simple formula combining the RIGHT, LEN and SEARCH functions:

Here, we have again searched for the position of the first space, and then use LEN function to calculate the character number of the names, and lastly we have used RIGHT function to extract the name from the right side.

4. Conclusion

Finally, we have tried to explain easy steps on how to separate names in Excel. Basically, you can find four main methods in this article. And, each of them has pros and cons for you to evaluate.

Therefore, we have tried to give you the main idea, not just the instructions. In this way we believe you can master your Excel skills and adapt to changing requirements.

Stay tuned for more Excel tips!

If you want to practice your Excel formulas, we have a very unique tool, which is also in Excel!