Thankfully, it’s easy to take those cells and split up the first name and last names so that you can use them in any way you wish. If you want to separate names in Google Sheets, follow the steps below.
How to Separate Names Using SPLIT
The SPLIT function in Google Sheets allows you to split a text string up by looking for a separator such as a space or a comma. In this example, we will assume that your name data is in the form FirstName LastName, but the method can be tweaked to work with other formats, such as LastName, FirstName. To separate names in Google Docs using SPLIT:
Click in the cell where you want the first part of the name to appear.Type =SPLIT( and select the cell containing the name. Type a comma, followed by a space in quotes, and then a closed bracket. If your name is separated by a comma, you would use a comma in quotes instead of a space. Press Enter—your name will appear separated. To apply your formula to the other names, click in the cell with your formula, and then click and hold on the small square in the bottom right-hand corner of the cell. Drag down to apply the formula to the other cells.
How to Separate Names Using the Split Text to Columns
The SPLIT function creates two new columns with the separated name data and leaves the original name intact. If you would prefer to replace the original name with the separated name, you can use the Split Text to Columns tool. To separate names using Split Text to Columns:
Highlight all of the cells containing your name data.Press Data > Split Text to Columns. Open the Separator dropdown menu. Select Space (or choose Comma if your name data is separated by commas).
How to Separate Names Using Text Functions
Both of the above methods create two columns of data, with one for the first name and one for the last name. In some cases, you may only want to extract the first or last name on its own. This is possible in Google Sheets by combining two different Google Sheets Functions. First, we use FIND to tell us the position of space within our text string. For example, if the space was the fifth character, FIND would return 5. Then we use LEFT to extract a set number of characters from the left of our string. Since we don’t want to include the space, we will select one less character than the number returned by FIND.
Extracting a First Name in Google Docs
To extract a first name in Google Docs using text functions:
Select an empty cell.Type =LEFT( and click in the cell containing your name data. Type a comma, followed by FIND(“ “, and then click on the containing your name data again. Make sure you type a space between your quotes. Insert a closed bracket, followed by -1 and a final closed bracket. Press Enter to extract the first name.
You can use the fill-down technique explained above to apply your formula to the other names.
Extracting a Second Name in Google Docs
The formula to extract the second name is slightly more complex. This is because the FIND function tells us the number of characters from the start of the string that the space appears. For the second name, we need to know how many characters from the end it appears, so we will subtract the position of the space from the total length of the text string. To extract a second name in Google Docs using text functions:
Click on the cell where you want the second name to appear.Type =RIGHT( and click on the cell containing the name. Insert a comma and type LEN( before clicking the cell again. Type a closed bracket, type -FIND(“ “, and click on the cell again. Ensure that you type a space between your quotes. Type two more closed brackets to complete the formula. Press Enter and the second name is extracted. Use the fill-down technique given above to apply the formula to your other cells.
Manipulating Data in Google Sheets
If you’re trying to separate names in Google Sheets, the steps above should help you to get the job done. There are other ways to manipulate your data in Google Sheets, however. You can alphabetize your data to get it all into order, or highlight duplicates to find any repeated data. You can also use other conditional formatting rules in Google Sheets to make key data stand out or use filter views to see only the data you want to examine. Comment
Δ
