Skip to Content

How to Separate First And Last Names in Google Sheets (Split Full Name)

Posted on Last updated:

Google Sheets is the perfect spreadsheet software to organize datasets. It allows users to split a text string into words. You can separate first and last names in Google Sheets by clicking on the “Data” tab of the main menu and selecting the “Split text to columns” options. (More on this soon.)

You may need to split the names into first and last names for various purposes, including sending an email campaign, creating invitation cards, and more.

If you try separating hundreds of first and last names manually, it will surely take more than several hours.

So, in this case, it is better to automate this task using Google Sheets. 

In this article, let us discuss the methods to separate names in google sheets. We will dive deep into the built-in features and functions of Google Sheets, so read the article until the end.

Here are the methods we will discuss: Click on the respective links to jump to that particular section of the article:

Get the Example Google Sheet

You can copy the following Google Sheet and follow me as I take you through this article.

Click Here to Copy!

It contains spreadsheets with example datasets used to demonstrate various functions to separate first and last names in Google Sheets.

If you have your own Google Sheet ready with the required dataset, then skip copying the above file.

How to Separate First and Last Name in Google Sheets

There are three methods to separate first and last names in Google Sheets.

You don’t need to be an expert to use these methods. Ensure you properly learn the syntax of the functions discussed in the following section.

We will start with the most common and easy method first.

Here is the table which contains the full name in column A.

Our task is to extract the first in column B and the last name in column C.

METHOD #1: Using the Split Text to Columns feature to Separate First and Last Names in Google Sheets

This method is pretty straightforward and less time-consuming compared to the other two methods discussed below.

You can split full names into first and last ones within seconds. All you need to do is define the delimiter, which acts as the separator for two or more words from a text string.

Since a space generally separates the first and last name, we will use it as a delimiter.

Here are the steps:

  • Open the Google Sheet
  • Select all the cells containing the full name
  • Hover to the main menu and click on the “Data” tab
  • Choose the “Split text to columns” option from the popup
  • You will see a small popup appear over the select column, as shown below
  • Click on the dropdown beside the Separator option
  • Select the “Space” from the popup

Google Sheets will instantly separate the first and last names from the full name.

As you can see in the above GIF, column A was holding the full names previously, but after using the Split text to columns tool, the full names in column A are replaced with the first names.

Note that you can also define a custom separator using the last option of the small popup.

PRECAUTION

Suppose we have full names in Column A; then it is compulsory for you to make sure that Column B is totally blank and does not contain any text strings or values while using the Split text to columns feature.

If the adjacent column holds any text strings or values, those are replaced with the data generated using the Split text to Columns tool.

METHOD #2 – Using the SPLIT function to Separate Full Name in Google Sheets

This is also a simple and easy-to-use method to split full names into first and last names.

It requires a complete understanding of the SPLIT function in Google Sheets.

So, let us first learn the SPLIT function.

Explained: The SPLIT function in Google Sheets

The SPLIT is a simple function in Google Sheets to divide a text string. You only need to provide the delimiter for this function to work.

Here is the general syntax of the SPLIT function,

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Each argument of the above function needs to be replaced with the proper information,

  • “text” – This argument needs to be replaced with the text string you wish to split. You can either manually type or provide the cell reference holding the text string
  • “delimiter” – Here, you can define the separator. It can be a letter or a sign. Most often, the delimiter is either a space (““) or a comma (“,”)
  • “split_by_each” – It is an optional argument that accepts two inputs: TRUE and FALSE. In the case of TRUE, the function considers each letter or sign from the delimiter argument while splitting the text string. Whereas, in the case of FALSE as an input, the function considers all the values defined in the place of the delimiter argument while splitting the text string
  • “remove_empty_text” – It is another optional argument in this function. You can input TRUE or FALSE and choose to display empty values in split texts. The TRUE means empty values are removed from the split texts, and FALSE will keep the empty values as is. By default, the function considers TRUE as an input for this argument

The syntax may feel a bit complex to you. But note that in the following section of this article, we won’t use optional arguments (split_by_each and remove_empty_text).

In other words, we will just define the text that needs to be split and delimiter, which acts as a separator.

Now, let us use this function to separate first and last names.

  • Click on the cell “B2“
  • Type “=split”
  • Select the first option from the popup or press “Tab” key
  • Replace the “text” argument with the cell reference “A2“, as it holds the full name we wish to split
  • Press “,” to move to the next argument
  • As the first and last names are separated by space in cell B2, for the “delimiter” argument, we will put ” “
    (Make sure to use double quotation marks while defining the delimiter; otherwise, the SPLIT function will return an error)
  • Now, we won’t touch the next two arguments and proceed with the default values for them
  • So, let’s directly use the “)” symbol and close the brackets
  • Press “Enter” key

Google Sheets will instantly split the full name into first and last names, as shown in the above GIF.

To apply this formula for the rest of the cells, you can either use the Auto Fill suggestion shown in the above GIF or hover over the Fill Handle of the cell “B2” until you see the “+” icon. Refer to the following image.

Click on that “+” icon and drag it to the end of the table.

METHOD #3 – Using the Combination of the LEFT, RIGHT, FIND, and LEN function

This is the final method on this list, which requires you to know the LEFT, RIGHT, FIND, and LEN functions in Google Sheets.

It is quite a complex method compared to the other two discussed above.

Let us start by learning each of these functions.

Explained: The LEFT function in Google Sheets

The LEFT is a simple function in Google Sheets that helps users extract the portion of a text string from the beginning.

Here’s the general syntax for the LEFT function:

=LEFT(string, [number_of_characters])

Each argument needs to be replaced with the proper information,

  • string” – This argument needs to be replaced with the text string from which you wish to extract the characters or symbols
  • “number_of_characters” – Here, you need to define the number of characters you wish to extract from the text string. By default, the function will extract the first character of the text string

The formula is pretty straightforward. Note that for the “string” argument, you can manually enter the values or provide the cell reference holding the text.

Explained: The RIGHT function in Google Sheets

The RIGHT function is similar to the LEFT function, except it is used to extract the portion of the text string from the end.

The general syntax for the RIGHT function is as follows,

=RIGHT(string, [number_of_characters])

Here’s a quick explanation for each argument of the above formula,

  • “string” – Represents the text string from which you wish to extract the characters
  • “number_of_characters” – This argument lets the user define the number of characters they wish to extract from a given text string

Both of these functions, the RIGHT and LEFT, are straightforward. You don’t need any expert knowledge to use them.

Explained: The FIND function in Google Sheets

The FIND function returns an integer value that represents the position of the given character within a text string.

It helps you locate a specific character.

The general syntax for the FIND function is as follows,

=FIND(search_for, text_to_search, [starting_at])

Here’s how you need to deal with the arguments of the FIND function:

  • “search_for” – It is pretty obvious from the name itself. This argument lets you define a specific character you wish to search for within the cell
  • “text_to_search” – Here, you need to provide the cell reference that holds the text string in which the function will look for a specific character
  • “starting_at” – This is an optional argument that lets users define the position from where the search should begin within the cell. By default, the function will start searching for the specific character from the position 1

This function is combined with various others in Google Sheets to perform complex calculations.

Explained: The LEN function in Google Sheets

This function is used to count the number of characters within a cell. It is a simple function with the following general syntax,

=LEN(text)

Here, the “text” argument needs to be replaced with the cell reference holding the text string.

Note that the function also counts letters, numbers, spaces, and special characters.

That’s it!

Now, let us use these functions to separate first and last names from the full names.

For the sake of this article, let us divide it into two steps.

Step 1 – Getting the first name from the Full Name

In this step, we will extract the first name using the LEFT and FIND functions in Google Sheets.

Let’s begin,

  • Click on the cell “B2“
  • Type “=left”
  • Select the first option from the popup or press “Tab” key
  • For the “string” argument, we will provide the cell reference as “A2“
  • Press “,” to move to the next argument
  • Type “find“

    (As the FIND is a function in Google Sheets, you will notice a popup appears as shown above)
  • Select the first option from the popup
  • For the “search_for” argument of the FIND function, we will put ” “
    (Make sure to use double quotation marks; otherwise, the formula will return an error at the end)
  • Press “,“
  • Provide the cell reference as “A2” in place of the “text_to_search” argument
  • Close the bracket for the FIND function using “)“
  • Now, you need to put “-1“
  • Close the bracket for the LEFT function using the “)“
  • Press “Enter“

Here’s what our final formula looks like,

=LEFT(A2,FIND(" ",A2)-1)

Step 2 – Getting the Last Name from the Full Name

Here, we will extract the last name from the full name using the RIGHT, FIND, and LEN functions in Google Sheets.

  • Click on the cell “C2“
  • Type “=right”
  • Select the first option from the popup or press “Tab” key
  • Provide the cell reference “A2” for the “string” argument
  • Press “,” to move to the next argument of the RIGHT function
  • Now, we will type “len“

    (As the LEN is a function in Google Sheets, you will see a popup as shown above)
  • Select the first option from the popup
  • Provide the cell reference as “A2” for the “text” argument of the LEN function
  • Close the bracket for the LEN function using the”)” key
  • Next, type “–” symbol
  • Type “find” and select the first option from the popup

    (As the FIND is a function in Google Sheets, you will see a popup as shown above)
  • Replace the “search_for” argument of the FIND function with the ” “
    (Make sure to use quotation marks while putting the space in the formula)
  • Press “,” key
  • Replace the “text_to_search” argument of the FIND function with the cell reference “A2“
  • Close the bracket for the FIND function using “)“
  • Similarly, close the bracket for the RIGHT function using the “)“
  • Press “Enter“

Our final formula should be as follows,

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Now that we have our first and last names extracted from the first full name in the cell “A2”, let’s apply the same formulas for the entire column.

Select “B2” and “C2”. Go to the lower-rightmost corner of the cell “C2” over the Fill Handle and double-click on it. It will instantly apply the formula for the entire column.

Conclusion

Separating first and last names from the full name shouldn’t be a hard task.

Using the Split text to Columns tool and the SPLIT function is pretty straightforward and quick.

I hope this article taught you everything when it comes to how to split first and last names in Google Sheets. Feel free to comment below if you are stuck somewhere or having any particular issue.