How-To

How to Separate Names in Google Sheets

Working in spreadsheets

Want to separate names in a Google Sheets spreadsheet? There are a few methods you can try. This guide will show you how.

You want to mail merge to send out personalized emails to your client list. The trouble is, all of your clients’ names are saved in Google Sheets with both names in a single cell, and ‘Dear John Smith’ just doesn’t sound that personal.

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:

  1. Click in the cell where you want the first part of the name to appear.
  2. Type =SPLIT( and select the cell containing the name.
    Using the SPLIT function in Google Sheets
  3. 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.
    Using the SPLIT function in Google Sheets
  4. Press Enter—your name will appear separated.
    Separated names in Google Sheets
  5. 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.
    Fill down in Google Sheets
  6. Drag down to apply the formula to the other cells.
    Filled down data in Google Sheets

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:

  1. Highlight all of the cells containing your name data.
  2. Press Data > Split Text to Columns.
    Splitting text in Google Sheets
  3. Open the Separator dropdown menu.
    Seperating text in Google Sheets
  4. Select Space (or choose Comma if your name data is separated by commas).
    Seperating text in Google Sheets

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:

  1. Select an empty cell.
  2. Type =LEFT( and click in the cell containing your name data.
    Using the LEFT function in Google Sheets
  3. 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.
    Using the FIND function in Google Sheets
  4. Insert a closed bracket, followed by -1 and a final closed bracket.
    Using the LEFT function in Google Sheets
  5. Press Enter to extract the first name.
    An extracted first name in Google Sheets

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:

  1. Click on the cell where you want the second name to appear.
  2. Type =RIGHT( and click on the cell containing the name.
    Using the RIGHT function in Google Sheets
  3. Insert a comma and type LEN( before clicking the cell again.
    Using the LEN function in Google Sheets
  4. Type a closed bracket, type -FIND(“ “, and click on the cell again. Ensure that you type a space between your quotes.
    Using the LEN function in Google Sheets
  5. Type two more closed brackets to complete the formula.
    A formula using RIGHT in Google Sheets
  6. Press Enter and the second name is extracted.
    Extracted text in Google Sheets
  7. 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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top