Holy sheet: How to split full names into first and last with Google Sheets

Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff.

Sometimes you’ve gotta work with what you’ve got — and that doesn’t just apply to the intelligence and skills you’ve respectively been given from nature or obtained from nurture. It can also apply to what has been referred to as the new oil: data. It’s what runs through the veins of the new economy, replacing the industrial with the digital.

And just as crude oil has to be processed to become a usable product, raw data needs to be cleaned or transformed— whether it’s to make it readable for users or just to ship it off on to the next stage of data processing.

So let’s zoom in on a common type of data, full names, and how we can separate them in bulk into first and last names. And we’re doing that without using any fancy programming tool, just some simple formulas in good ol’ spreadsheets.

An often provided solution

Usually full names consist of two parts: a first name and a last name.

So the simple solution most people turn to, is find the space in the full name and then splitting the full name by that character… but this doesn’t really work. Some full names might contain multiple spaces — so this commonly provided solution (looking at you internet) doesn’t take longer names into account. Ergo, not good enough.

A better solution

So, how can we do this in a better way, accommodating all lengths of full names? Firstly, we have to decide what we consider the first name part, and what the last name part. For automation/bulk purposes, let’s go with the following: everything before the first space in the full name is the first name, and everything after the first space is the last name.

This way, the full name ‘Jose Domingo Sanchez’ would be split into the first name ‘Jose’ and the last name ‘Domingo Sanchez’. Again, this would work for longer names as well.

In Google Sheets, we can use the following formula to extract the first name:

=index(split(A2, “ ”), 1)

In cell B2 in our example below, we refer to the text in cell A2 where the full name is written, to be split up by a space character. But we’ve encapsulated this function with an index function, and point it to index number 1.

This means that the full text won’t be split among several columns based on the space character, but that we only extract the first word that’s separated from the following with a space character, leaving Jose in B2.

Next, we want the remainder of the full name to be inserted into cell C2… and here’s where the magic happens. Instead of using the split function, we can simply subtract the first name from the full name:

=trim(REGEXREPLACE(A2, B2, “”))

In cell C2 we use the regular expression replace formula (REGEXREPLACE) and refer to the starting text in A2 — then to the first name in B2 as the text which needs to be replaced in the starting text — and then type an empty string (two double quotes without anything between it) to make sure the first name part in the full name gets replaced with nothing.

The formula is encapsulated with a trim function so that we remove the space character that would precede the last name, because we subtracted just the first name from the full name, not the space in between.

So there you go, a solution to split full names into first and last names, no matter how long the names. Time to split some names yourself!