In this article:

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

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.

Separated names 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 

  1. Select the first cell beside the cell you want to separate.
  2. Input =SPLIT(cell reference, "delimiter") inside cell and press enter
Using SPLIT on Google Sheets

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,” “).

  1. Drag the fill handle across cells below.
Output of function in Google Sheets

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

  1. 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.

  1. 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.

  1. Copy the formulas down to complete.
Output in Google Sheets

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. 

Add-on in Google Sheets

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

Activating in Google Sheets

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.

Setting up Google Sheets add-on

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

Output of Google Sheets Add-On

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!

Use our timesheet approval software to easily set up custom reminders from your spreadsheet in just a few clicks. 

If you enjoyed this article, you might also like our article on how to split text to columns or our article on how to split columns in Google Sheets. 

If you want to learn how to send emails when a new row is added in Google Sheets, we also suggest checking out our detailed guide. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started