Excel is an amazing tool when it comes to slicing and dicing text data. There are so many useful formulas as well as functionalities you can use to work with text data in Excel. One of the very common questions I get about manipulating text data is – “How to separate first and last names (or first, middle, and last names) in Excel?“. There are a couple of easy ways to split names in Excel. The method you choose will depend on how your data is structured and whether you want cowothe result to be static or dynamic. So let’s get started and see different ways to split names in Excel.
Split Names Using Text to Columns
Text to Columns functionality in Excel allows you to quickly split text values into separate cells in a row. For example, suppose you have the dataset as shown below and you want to separate the first and last name and get these in separate cells.
Below are the steps to separate the first and last name using Text to Columns: The above steps would instantly split the names into first and last name (with first names in column B and last name in column C).
Once done, you can delete the full name data if you want. A few things to know when using Text to Columns to separate first and last names in Excel: Text to Columns option is best suited when you have consistent data (for example all names have first and last name only or all names have a first, middle, and last names). While Text to Columns is a fast and efficient way to split names, it’s suited only when you want the output to be a static result. In case you have a dataset that may expand or change, you are better off using formulas to separate the names.
Separate First, Middle, and Last Names Using Formulas
Formulas allow you to slice and dice the text data and extract what you want. In this section, I will share various formulas you can use to separate name data (based on how your data is structured). Below are the three formulas you can use to separate first, middle, and last name (explained in detail later in the following sections). Formula to get the first name: Formula to get the middle name: Formula to get the last name:
Get the First Name
Suppose you have the dataset as shown below and you want to quickly separate the first name in one cell and last name in one cell.
The below formula will give you the first name:
The above formula uses the SEARCH function to get the position of the space character in between the first and last name. The LEFT function then uses this space position number to extract all the text before it. This is a fairly straight forward use of extracting a part of the text value. Since all we need to do is identify the first space character position, it doesn’t matter whether the name has any middle name or not. The above formula is going to work just fine. Now, let’s get a little more advanced with each example.
Get the Last Name
Let’s say you have the same dataset and this time you need to get the last name. The below formula will extract the last name from the above dataset:
Again, quite straightforward. This time, we first find the space character position, which is then used to find out the number of characters that are left after space (which would be the last name). This is achieved by subtracting the position value of the space character with the total number of characters in the name. This number is then used in the RIGHT function to fetch all these characters from the right of the name. While this formula works great when there is only the first and last name, it wouldn’t work in case you also have a middle name. This is because we only accounted for one space character (between first and last name). Having a middle name adds more space characters to the name. To fetch the last name when you have a middle name as well, use the below formula: Now, this has started to become a bit complex… isn’t it? Let me explain how this works. The above formula first finds the total number of space characters in the name. This is done by getting the length of the name with and without the space character and then subtracting the one without space from the one with space. This gives the total number of space characters. The SUBSTITUTE function is then used to replace the last space character with an ‘@’ symbol (you can use any symbol – something which is unlikely to occur as a part of the name). Once the @ symbol has been substituted in place of the last space character, you can easily find the position of this @ symbol. This is done using the SEARCH function. Now all you need to do is extract all the characters to the right of this @ symbol. This is done by using the RIGHT function.
Get the Middle Name
Suppose you have the dataset as shown below and you want to extract the middle name.
The following formula will do this:
The above formula uses the MID function, which allows you to specify a start position and the number of characters to extract from that position. The start position is easy to find using the SEARCH function. The hard part is to find how many characters to extract after this start position. To get this, you need to identify how many characters are there from the start position to the last space character. This can be done by using the SUBSTITUTE function and replace the last space character with an ‘@’ symbol. Once this is done, you can easily use the SEARCH function to find the position of this last space character. Now that you have the starting position and the position of the last space, you can easily fetch the middle name by using the MID function.
Separate Names Using Find and Replace
I love the flexibility that comes with ‘Find and Replace‘ – because you can use wild card characters in it. Let me first explain what’s a wild card character. A wildcard character is something that you can use instead of any text. For example, you can use an asterisk symbol () and it will represent any number of characters in Excel. To give you an example, if I want to find all the names that start with the alphabet A, I can use A in find and replace. This will find and select all the cells where the name starts with A. If you’re still not clear, don’t worry. Keep reading and the next few examples will make it clear what wildcard characters are and how to use these to quickly separate names (or any text values in Excel).
Get the First Name
Suppose you have a dataset as shown below and you want to get the first name only.
Below are the steps to do this: The above steps would give you the first name and remove everything after the first name.
This works even if you have names that have a middle name.
Get the Last Name
Suppose you have a dataset as shown below and you want to get the last name only. Below are the steps to do this: The above steps would give you the last name and remove everything before the first name. This works even if you have names that have a middle name.
Remove the Middle Name
In case you only want to get rid of the middle name and only have the first and the last name, you can do that using Find and Replace. Suppose you have a dataset as shown below and you want to remove the middle name from these. Below are the steps to do this: The above steps would remove the middle name from a full name. In case some names don’t have any middle name, they would not be changed.
Separate Names Using Flash Fill
Flash fill was introduced in Excel 2013 and makes it really easy to modify or clean a text data set. And when it comes to separating names data, it’s right up in Flash Fill’s alley. The most important thing to know when using Flash Fill is that there needs to a pattern that flash fill can identify. Once it has identified the pattern, it will easily help you split names in Excel (you will get more clarity on this when you go through a few examples below).
Get the First or the Last Name from Full Name
Suppose you have a dataset as shown below and you want to get only the first name. Flash Fill needs you to give it a pattern that it can follow when giving you the modified data. In our example, when you type the first name in the first cell, Flash Fill can’t figure out the pattern. But as soon as you start entering the First name in the second cell, Flash Fill understands the pattern and shows you some a suggestion. If the suggestion is correct, just hit the enter key. And if it’s not correct, you can try entering manually in a few more cells and check if Flash Fill is able to discern the pattern or not. Sometimes, you may not see the pattern in gray (as shown in step 2 above). If that’s the case, follow the below steps to get the Flash Fill result: The above steps would give you the result from Flash Fill (based on the pattern it has deduced). You can also use Flash Fill to get the last name or the middle name. In the first two cells, enter the last name (or the middle name) and flash fill will be able to understand the pattern
Rearrange Name Using Flash Fill
Flash Fill is a smart tool and it can decipher slightly complex pattern as well For example, suppose you have a dataset as shown below and you want to rearrange the name from Rick Novak to Novak, Rick (where the last name comes first followed by a comma and then the first name). Below are the steps to do this:
Remove the Middle Name (or Just get the middle name)
You can also use Flash Fill to get rid of the middle name or get only the middle name. For example, suppose you have a dataset as shown below and you want to get only the first and the last name and not the middle name. Below are the steps to do this: Similarly, if you only want to get the middle names, type the middle name in the first two cells and use Flash Fill to get the middle name from all the remaining names. The examples shown in this tutorial uses names while manipulating the text data. You can use the same concepts to also work with other formats of data (such as addresses, product names, etc.) You may also like the following Excel tutorials:
Extract Usernames from Email Ids in Excel How to Filter Cells that have Duplicate Text Strings (Words) in it Convert Date to Text in Excel Convert Text to Numbers in Excel How to Remove the First Character from a String in Excel How to Sort by the Last Name in Excel How to Combine First and Last Name in Excel Extract Last Name in Excel (5 Easy Ways) Separate Text and Numbers in Excel How to Switch First and Last Name in Excel with Comma?
How can we split those using formula Sonia (if I use current formula then it will #value error) Divya Joseph Manali Shah 🙂
