How to Split First and Last Name in Google Sheets (Easiest Way)

7 Minutes

It is very common to have a list of full names you want to split up: event registration, email lists, and even company rosters. In this article, we show you step-by-step how to split first and last name in Google Sheets using the text to columns tool, SPLIT function, string functions, and a popular add-on.

Method 1: Split Names Using Text to Columns

One of the shortest ways to do this task is by using Text to Columns, a built-in feature in Google Sheets that separates your text with a delimiter. A delimiter is something that separates two values. Thankfully, since first and last names are separated by spaces, this would be a perfect delimiter.

‍

1. Select your full names in Google Sheets

2. Go to Data -> Split Text to Columns

*Separating in Google Sheets Text to Column*

3. Beside your column, select space as your delimiter.

*Setting delimiter to separate in Google Sheets*

4. Now you have your names in separate columns in Google Sheets.

Note that by using this, your first names will occupy the first column and will remove the full names. It will also treat your headers the same way. Luckily, there are other methods in Google Sheets that we will cover below.

Method 2: Separate First and Last Name using SPLIT Function

This function in Google Sheets performs the same technique to separate that the text to column uses. Given a certain delimiter, this function will divide your text in every instance thereof.

‍

Syntax of the Function

‍

The Split function in Google Sheets is in the following form:

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

‍

Its main parts would be:

TEXT which is the full name,

‍

DELIMITER which would be the space enclosed in quotation marks (“ “),

and two other optional arguments in brackets that are not necessary in dividing simple names.

‍

To use this formula, simply follow this guide:

‍

Using the Split Function

Select the first cell beside the cell you want to separate.
Input =SPLIT(cell reference, "delimiter") inside cell and press enter

Here the cell reference is the name we want to split, cell A2, and our delimiter will be the space. Thus, we use =SPLIT(A2,” “).

Drag the fill handle across cells below.

Method 3: Split Names Using Functions on Text Positions

Another powerful combination of functions in Google that can help you sort your names will be using the FIND, LEFT, RIGHT, and LEN Functions.

‍

Syntax of String Based Functions

Let’s look at them all before using them together:

‍

LEFT and RIGHT are used in strings to return all characters to the left or right of a specified position within the string. It follows the syntax

‍

=LEFT(string, [number_of_characters])

=RIGHT(string, [number_of_characters])

String is a set of words or characters (reference the cells containing the full names).

Number_of_Characters is the count of characters from the left or right ends of our string. Since the number of letters in a first name or a last name vary per name, using these two alone will not be sufficient. This is why we use the FIND and LEN Function.

‍

FIND allows you to look for a particular character in the middle of a string and return its position as a number. Its syntax is =FIND(search_for, text_to_search,[starting_at])

‍

We will use FIND to search for the first space from the full names.

‍

LEN shows the total count of characters in a cell and its syntax is =LEN(text)

‍

Follow the steps below to discover how to string these formulas in Google Sheets.

‍

Using a String of Functions to Separate Names

To find the first name, go to adjacent cell and input
=LEFT(cell reference,FIND(“ “,cell reference)-1

*Using LEFT function to get first name in Google Sheets*

Our cell reference will be the cell containing the name. We subtract 1 to indicate the position excluding the space. This will return characters at the left before the space, our first names.

‍

To find the last name, go to cell for last names and input =RIGHT(cell reference,LEN(cell reference)-FIND(“ “, cell reference))

*Using RIGHT function to get last name in Google Sheets*

This works by subtracting the position of the first space to the number of characters. It then gives us all the characters to the right of our string.

Copy the formulas down to complete.

Now we have our first and last names separated. However, some cases of names can be more complex with middle names, double-barreled surnames, suffixes, and more. Dealing with these types of names will not be as simple as splitting them where spaces are found. Thankfully, we have an Add-On on Google Sheets called Split Names that will help us with these types of cases. This is a paid subscription but there is a free trial and here's how to install and use it.

Method 4: Split Names in Google Sheets Using an Add-On

1. Go to Extensions -> Add-Ons -> Get Add-Ons and search Split Names by Ablebits and install.

2. Once installed, select your columns of names and go to Extensions -> Split Names -> Start

3. In the pop-up, you will be able to select if your data has headers and what kinds of additional columns you want then press Split.

4. The Add-on recognizes them and will divide them into their respective columns.

And those are the different ways to split the full names into first and last names in Google Sheets. Try the different methods to see which is best for your case use and good luck!