While working on data sets you often come into a situation where you need to split or separate the first and last names from the cells and Google Sheets has this feature available to split or separate the first and last names quickly.
In this tutorial, we will show you how to separate the first and last names in Google Sheets in multiple ways.
Steps to Separate First & Last Name Using Split Text to Columns Option
The quickest and easy way to separate the first and last name in Google Sheets is by using the split text to columns option.
- First, select the cell range that contains the full names.
- After that go to the “Data” tab and then click on the “Split text to columns” option from the drop-down menu.
- Now, click on “Detect automatically” and then click on “Space” as a separator type and your last name will get split into the next column.
Note: As you saw, last names got split to the very next column so before separating the last names, always remember to add the column with blank cells otherwise the last names will get split to the next column’s cells and the data of that cells will get replaced with the last names and we have chosen the “Space” as a separator because in our case first and last names were made with space as delimiter.
Split First and Last Name Using Split Formula
You can also use the SPLIT formula to split the first and last names. The SPLIT formula splits the first and last names into two separate cells without changing the actual full names.
- First, add two columns with blank cells to split the first and last names over there.
- After that, write the split formula in the cell where you have to split the first name as shown in the image and press enter.
- Once you press enter, you will get the first and last name got split with the original remaining unchanged.
- Now, select both the cells and then double click on the right below the corner of the cell as shown in the image and your formula will get copied into the remaining cells.
- At this stage, your first and last names got slit into the different cells.
- In the end, select and copy the entire cell range with first and last names and right click and select the “Paste Special” option and then choose the “Values Only” option to remove the formula from the cells.
Note: The difference between using the “Split text to column” option or “Split function” is that the “split formula” splits both the first and last names into the new cells whereas the “split text to column” option splits only the last name to the new cell.
How Split Formula Works
Let us take the SPLIT formula used in the above example to show you how the SPLIT formula works.
=SPLIT(A2,” “)
In the formula, first, we have given the cell reference A2 in the formula from where we need to split the names. After that, we used the space as a delimiter within double quotations because the differentiator or delimiter between the first and last names is the space between them. If the full names would have been with other delimiters like commas or semi-column then we would have used that delimiter instead of space.
Extract First Name Using Left Formula
You can use the combination of LEFT and SEARCH formulas if you want to extract the First name from the full name as you show in the below image.
=LEFT(A2,SEARCH(“ “,A2)-1)
In the first part of this formula, the SEARCH function finds the position of the space and returns the number count for that, and minus one we have used to subtract one from the returned number to get the number count up to the last character of the first word and in the second part LEFT function extracts the characters starting from the left up to the returned number count, means the first name before the space delimiter.
Extract Last Name Using Right Formula
You can use the combination of RIGHT, LEN, and FIND formulas if you want to extract the last name from the full name as you show in the below image.
=RIGHT(A2,LEN(A2)-FIND(" ",A2))- In the first part of this formula, the LEN function counts the length of the total characters of the name including the space between them, after it FIND function finds the position of the space and returns the number count for that space.
- Then, we used the minus sign between LEN and FIND function which subtracts the total length of the characters from the length count up to the space which returns the number count of the last name characters and in the end RIGHT function extracts the last name based on that number.