How To Separate First and Last Name In Excel & Google Sheets: While using an Excel sheet for your work, if you are a regular user of it then some time or the other you might have faced the situation where you have a name in a single cell but you wanted to separate the name into the different cells of it. And if not yet then in the future you are going to face the same issue for sure since it is a quite common problem in Excel. And to resolve this problem you might have googled multiple times to get the result of various macros by various people.
How To Separate First and Last Name In Excel & Google Sheets
However, while you read the post below, you will find about how with a mere onetime setup of a formula, this problem is going to resolve within seconds, and this way you can also learn an advanced function to imply with your excellent work and do some interesting things with your data.
On the other hand, if you don’t like using formulas want an Instant solution then you can find it by scrolling down from text to column section and you will come to know how to use Excel features to resolve this problem and the illustration attached with it simplifies your learning as well. So, go through the complete article till the end and you will clear with separate first and last names in excel.
Separate Names in Excel
Before we discuss the process to separate the first and last name in Google Sheets, let us see how names are stored in excel on a regular basis.
In general, you can find two ways of names coming together in a cell.
The first one is firstname and lastname with just space
Second firstname and lastname with a comma (,) in middle
The other 3rd way you can find is firstname middle initial last name as you can see in the below-given illustration:
By using the simple formula and combining a couple of them together you can easily separate these names in separate cells in excel.
As a first initiative, let’s extract the first part of the name (firstname).
In this case, let’s use two functions: LEFT and SEARCH. And here is what we exactly have to do.
Use this formula for your work to be easily done:
- =LEFT(NN,SEARCH(“ “,NN)-1
NN = cell with the name in it and -1 is for removing extra space or comma at the end of the string.
You have noticed that we used a LEFT function which is based on two arguments: the sting and the number of characters that you will have to grab starting from the starting of the string.
In the 1st case, we searched the space first by using a double quote with the placement of space in between.
While in the 2nd case we put a comma since we are searching for a comma but no space in between.
And hence what is the result of all the 3 scenarios illustrated above?
As per the result is shown above you can see that in all the 3 cases, we have got the resulted first name in the next column and this result shows the name which was present in the first place from both the first and last name present in the cell.
For the best part here is what you can do further:
First of all search the cell for space or comma, then find its position, and last subtract its position from the total length of the string. Let’s see it with the illustration below:
- =RIGHT(NN,LEN(NN) -SEARCH(“ “,NN)
This time we use the RIGHT function. This takes 2 arguments as well:
The string and the number of characters that you want to grab from the end of the string going left. Here you should have the length of the string minus, the position of the space, or the comma.
Great, now we have the second part of the name! In the first two cases, you are doing a lot of work, but if there is some middle in the name, you can see that the result still includes the last name with the middle name. So how do we get only the last name and get rid of the middle earliest? Easy! Just again run the same formula that we got in the second section of the name.
So we are just doing another right and this time the joint medium initial and last name is implementing the formula on the cell. It will find space after the middle initial and then take the length from zero to the end of the string until the number of characters will be taken.
So there you have it! You have now split the first name and last name into different columns using excerpts in Excel! Obviously, not everyone will have their own lessons like this, but you can easily edit them to suit your needs.
Text to Columns
There is one more easy way to separate the composite text into different columns in MS Excel. It is in the form of a painted text column and it works very well. If you have a column that has more than two data then it is even more efficient.
For example, I have some data below where there are 4 pieces of data in one line and 5 pieces in the second row. I would like to divide it into 4 columns and 5 columns, respectively. As you can see, trying to use the above sources would be impractical.
First select the column you want to separate, then click on the tab in the next step and then click on the text to the column as shown below.
You will be presented a text to column wizard in the next step. In the first step, choose whether the field is delimited or has a fixed width. Here in our case, we will choose Delimited.
Choose the option of Delimiter, on the next screen. You can pick from tab, semicolon, space, and a comma or type a custom one in.
After all, this chooses the data format for the column finally. General will work fine for most types of data. And if you have something special like dates, then choose the format
Click finish at the end and watch how your data is magically separated into columns. You can see how one row turned into 5 columns and the other one into 4 columns. The text to a column is more powerful and can make your life a lot easier.
Video Tutorial: Separate First and Last Name in Excel 2021
Video Tutorial: Separate First and Last Name in Google Sheets
While you have gone through the article above you might be clear by now that how you can easily separate first and last name in excel and you are also given a clear illustration which you can use simply refer to understand it the best. It is just a little formula which you can use and have hands-on it and If yet are confused then simply make practice and then you will be clarified.
Hope by now you have got the idea of how you can resolve your issue related to Separate first name and last name in excel and if yet you are unlearned and want to get help then simply you can drop a comment in the remark section below and you will be assisted soon possible.