Ima guess this has probably happened to you: you've got a giant list of full names in a spreadsheet, names like "Tina Popintosh". Unfortunately, you need to isolate just that person's first name or their last name. Maybe it's for a mail merge, say if you want to send out messages that read "Dear Tina,". 


But all you have are full names, so you start doing some manual data entry, typing "Tina" into the First Name column, then tying "Popintosh" into the Last Name column. Then, you move onto the next name, all the while thinking: "I could imagine a robot doing this job."

That's your cue; whenever you can picture a robot doing a repetitive task in Google Sheets, it means there's probably a way to automate it. In this case, there are two ways.




The SPLIT Function

This function SPLITS the contents of a cell at a point (or points) that you specify. Like all Sheets functions, it requires pieces of information (arguments) that tell it how to do its job

 In this example, I have some names in column A that I want to SPLIT, so I start by typing my function in cell B2. Let's go through what arguments SPLIT needs in order work. Feel free to follow along in the example spreadsheet.

jFVD2RHTNyCYMu8Zi12bvjE_OyA89vPPBA.png


  1. First you need text, the data that you want to split. In our example that will be cell A2.
  2. Next, you need the delimiter. This is the spot where you want Sheets to SPLIT your data up. In our case, we want to split our data at the space character, so we type a space in quotation marks: " ". The quotation marks are important, you always need to include these.
  3. Finally, there's the split_by_each argument, which is optional. I recommend you ignore it.
So, here's our completed function, which will take the data in A2 and SPLIT it every time it sees a space " ": 

7ipe42PTh63YG7Z5xuHCNzSLpxQA9cd5Tw.png


Let's hit 'enter', then copy our function down the column:

vH8zBfzE6-f-nnUOZYjwwH23I7gD3-zJhA.png


Boom! Names divided! 


The LEFT and MID Functions
There's another way to split first and last names apart, but it takes a few more functions and a little more time. The upside of using the LEFT or MID function, however, is that you can isolate JUST the first or last name from a full name. 

To Isolate the First Name, use FIND and LEFT
1. First, create a new column just to the right of your existing data called FIND. You want to use the FIND function to find the location of the space character in your Full Name.
In our example, the function will read =FIND(" ",A2) which, in plain English, says "Go FIND the space character in cell A2 and tell me where it is."

koD3VphqZpFelEE4WhhzTepS6vM1ynM6aQ.png


2. The FIND function will return where the space character is located in your cell. In our first example, it's the 5th character (E, L, L, E, space), so the result is the number 5. 

3. We're going to switch over to our First Name column and we're going start typing =LEFT

UzQ-YxUas-zMgmzvE3-PvPSpCoR8KG1b3Q.png


3. LEFT needs two arguments. The first argument, 'string', should be the cell where you're pulling the first name from. In our example, that's A2.
The second argument [number_of_characters] is how many letters you want to bring over from cell A2. This is going to be the position of the space character (which we just calculated in cell G2), minus 1 (because we don't want the space). So, our function will be =LEFT(A2,G2-1)

O6LADnlECk8pfvdw3kCnCmfHj2Hyr3dsuA.png


4. If we hit Enter, then copy both of our formulas down their columns (both LEFT and FIND), we'll get our first names:

-wrVClVwr8cs4sU96OySGWABgEpNj4wfTw.png


To Isolate the Last Name, use FIND, LEN, and MID
1. Create a new column labeled LENGTH. Then, like we did with FIND, we're going to type the =LEN function into the first cell. This function is pretty easy to use. You point it at a cell, and it tells you how many characters are in the cell: =LEN(A2) will tell you how many letters are in A2.

5iynBLLfJZOBONQGmFjcYdR4qDviCVGMdw.png


2. Next, we're going to isolate the Last Name using the MID function. This function works like a pair of scissors, snipping out the middle portion of a cell's contents. It takes three arguments. First, you tell it where your target name is. In this example, that's cell A2. 

VoQwuwK-8P_tPUP1h5azzj94eCVtbaX6VQ.png


3. The MID function needs starting and stopping points for its scissors; where should it start cutting and where should it stop? Well, you want it to start after the space character (which, we've already used the FIND function to locate). In our example, we want to start cutting at the location of the space character (in cell G2), plus 1:

9ypuVJm43aIlebkzPZ6oIYtHJ8KogkmgQw.png


4. Finally, we need to tell MID where to stop cutting. This is the last character in your cell, or (wait for it) the LENGTH of your cell's text. We also have this number ready, calculated by the LEN function in cell H2:

KMPM52HJ-sM_cMi2sJ8m6zYTOCj22_vFKQ.png


5. Our formula is all set, so we can now hit Enter and copy it (and the LEN function in column H) down the column to get our last names:

dDyfjeHXiR5ZzrMfYti9aQf_0Y2Pdupi5w.png



FINAL IMPORTANT NOTE- You Need Values

You're not quite done at this point. Whether you used SPLIT or the LEFT-MID-FIND-LEN technique, you still have one more step to take.

These functions you've created will all continue to work just fine, but only as long as the data in column A doesn't change. If we get cavilier and decide to delete column A, thinking "Great, we don't need the full names anymore!", watch what happens to all of our hard work:

8MkMD1L9eJzD6bFxG6u_7_NuFGzZBK4bqw.png


Ahhh! To prevent this from happening, you need to do one more thing. Highlight all of the first and last names that you've extracted, copy them (Ctrl + c) and then paste them as values (Ctrl + Shift + v). This will get rid of all the formulas you made, but it will preserve the results, the names, which is what you really want.