Google Sheets – How to Separate the First Name from a Full Name Cell

left, right, find, length

Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined. 

This fairly simple process can be achieved with the Google Sheets formulas left, right and find. 

If you are in a hurry, here are the formulas below: 

First Names

To get the first name we do the following: 

=LEFT(A2,FIND(” “,A2))

Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.

When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does. 

FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item.  To do this FIND takes two arguments: 

=FIND(the item we are searching for, the cell or string the item is in)

For example, if we are searching for the location of the space in
 Vasco Nunez de Balboa  which is in cell A2,  we would do the following:

=FIND(” “,A2)

Which would give the result: 6

There are five letters in the first name Vasco. The space would be in position 6. 

Now that we have the position of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT. 

LEFT also take two values. The first is cell location and the second is the number of characters we want to take from the left-hand side. 

LEFT(cell location, number of characters from the left)

Now that we know the first space is character 6, the formula would look like this:

LEFT(A2,6)

We then replace the 6 with our FIND formula and we are good to go.

Other Names

To get all the names except for the first name requires us to go from the RIGHT. Right also take two arguments, the cell, and the number of characters, this time, from the right. 

The problem here is that FIND searches from left to right so we can’t simply use the same set of formulas. We first need to find the LENgth of the string of characters in the cell. 

Let’s go ahead and get the length of A2, Vasco Nunez de Balboa:

LEN(A2)

Which results is a length of: 21

Now we can subtract this length from our FIND formula to get the number of characters from the right the first space in the cell is. 

LEN(A2)-FIND(” “,A2)

or:

21 – 6 = 15

That’s 15 characters from the right. We can now go ahead and grab all but the first name:

RIGHT(A2,LEN(A2)–FIND(” “,A2))

Which would result in: 

Nunez de Balboa

 

Hope you found this useful. What project did you use this in? It’s always great to hear the creative way people use these formulas.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

~Yagi