If you use Excel a lot, you’ve probably come across a situation where you have a name in one cell and need to split the name into different cells. This is a very common problem in Excel, and you can probably do a Google search and download 100 different macros written by different people to do it for you.
However, in this post, I’ll show you how to set up a formula so you can do it yourself and really understand what’s going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so you can do more interesting things with your data.
If you don’t like formulas and want a faster solution, scroll down to the Text to Columns section, which explains how to use the Excel function to do the same. In addition, the function to convert text to columns is also best used if there are more than two elements in a cell that need to be split. For example, if 6 fields are combined in one column, using the formulas below will get really messy and difficult.
Separate names in Excel
First, let’s take a look at how names are commonly stored in an Excel spreadsheet. The most common two ways I’ve seen are first name, last name with a space and last name, first name with a comma separating them. Whenever I saw the initials in the middle, it was usually the intermediate last name of the first name, as shown below:
By using a few simple formulas and combining several of them, you can easily separate the first, last and middle initials into separate cells in Excel. Let’s start by extracting the first part of the name. In my case, we will be using two functions: left and search. Logically, we need to do the following:
Find a space in the text or a comma, find a position and remove all letters to the left of that position.
Here is a simple formula to get the job done right: = LEFT (NN, SEARCH (“”, NN) – 1), where NN is the cell that stores the name. -1 is used to remove extra space or comma at the end of a line.
As you can see, we start with a left-hand function that takes two arguments: a string and the number of characters you want to capture, starting from the beginning of the string. In the first case, we search for a space by using double quotes and placing a space between them. In the second case, we are looking for a comma instead of a space. So what is the result for the three scenarios I mentioned?
We got the first name from line 3, the last name from line 5, and the first name from line 7. Great! So, depending on how your data is stored, now you have extracted either the first name or the last name. Now for the next part. Logically, this is what we need to do now:
– Look for a space or comma in the text, find the position, and then subtract it from the total length of the string. This is what the formula will look like:
= RIGHT (NN; LEN (NN) -SEARCH (â€â€œ, NN))
So now we are using the correct function. This also takes two arguments: the string and the number of characters you want to capture, starting at the end of the line going to the left. So, we need the length of the string minus the space or comma position. This will give us everything to the right of the first space or comma.
Great, now we have the second part of the name! In the first two cases, you are almost done, but if there is a middle initial in the first name, you can see that the result still includes the last name with a second initial. So how do we just get the last name and get rid of the initial in the middle? Easy! Just run the same formula again that we used to get the second part of the name.
So we just do one more thing correct and this time apply the formula to the combined cell of the middle initial and last name. It will find a space after the middle initial and then take the length minus the space position, the number of characters from the end of the string.
That’s all! Now you have split the first and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have this text format, but you can easily edit it to suit your needs.
Text to Columns
There is also another easy way to split merged text into separate columns in Excel. It’s a feature called Text to Columns and it works really well. It is also much more efficient if you have a column that contains more than two pieces of data.
For example, below I have some data where one row contains 4 pieces of data and the other row contains 5 pieces of data. I would like to split this into 4 columns and 5 columns respectively. As you can see, trying to use the above formulas is impractical.
In Excel, first select the column you want to split. Then go to the Data tab and click Text to Columns.
This will bring up the Text to Columns wizard. In step 1, you choose whether the box is delimited or fixed width. In our case, we will choose with delimiters.
On the next screen, you will select a separator. You can select tab, semicolon, comma, space, or enter an arbitrary value.
Finally, you choose the data format for the column. Generally, General is fine for most data types. If you have something specific like dates, choose this format.
Click “Finish” and see how your data is magically divided into columns. As you can see, one row has become five columns, and the other has become four columns. The function of converting text to columns is very powerful and can make your life much easier.
If you have problems with name separation not in the above format, please leave a comment with your details and I will try to help. Enjoy!
–