Efficient spreadsheets in the classroom – Split & Combine Names

Sometimes, when we are using spreadsheets, our data comes from different places or we decide we would like to look at our data in different ways.  One very common case with this is around student names.  

Separating names and other data

One of the very useful features that we can use in spreadsheets is the SPLIT function. Let me talk to you about what that means. For example, sometimes student names are put into a single column where we have first name and last name.  What if want to have only their first name show up or only their last name show up by itself?

Maybe we’re making a name chart or labels and we don’t need the full name.  We could look at our list and start typing the first name in one column, then the last name in another column.  But this would be silly and take up time.  Why not get the spreadsheet to do the heavy lifting for you?

Leveraging the power of the split function

So instead, what we can do is we can use a formula called SPLIT. The way that the split formula works is like this.

1. Go to the cell where you want to separate your names.  

2. Type in the = sign here and type in the word SPLIT.

3. Type a ( sign or you can just press return on the keyboard.  This will start your formula.

4. Click on the cell that has the name in it.  That cell address should now be in your formula and change color.

5. Type a comma.

You now have to tell the formula how to divide up the data in the cell.  In our situation, we have first name and last name separated by a space.  We can add that to our formula by typing double quotes-space-double quotes ” “.  This is the representation for a space in a formula.  Press return and the name will separate over the next two columns.

If your names are entered where it is something like Riley, James with the last name, comma, first name, instead of using ” “ as the delimiter, you would type “, “.  This way, the names would be separated by the comma-space combination.

Side note: If you remember from the cell reference article, when I hover over the square in the lower right hand corner our cursor will change to a crosshair. I can click and drag down. The formula will copy down once I release it.

There are more complicated things you can do as well.  However, this is the basics for getting started with name splits. There are lots of other situations that you might run into that make it where you have to do some modifications, but this is the simplest form that you can use.

Putting data together in a single cell

Let’s take a look at another situation. Say you have a bunch of names that are already split into first name and last name. You want to combine those together. I find that maybe I don’t want just the first or last. I actually want the full name. We can do that too. There’s another formula for this called CONCATENATE. This combines multiple cells together.  There is another formula called CONCAT.  This is similar but only combines two pieces of data.  CONCATENATE combines many cells or data together.

1. Go to the cell where you want to input your data.

2. Type in the = sign here and type in the word CONCATENATE.

3. Type a ( sign or you can just press return on the keyboard.  This will start your formula.

4. Click on the cell that has the first name in it.  That cell address should now be in your formula and change color.

5. Type a comma

6. Then click on the second piece of data, for example the last name.

7. Close your parenthesis or tap return on your keyboard.

The new version of Google Sheets will sometimes recognize formulas and suggest autofill.  If it is something you want to do, you can click on the check mark and it will complete this process for you.  If not, just click the X.

Something's not quite right

In our example, are you able to notice a problem here? You see how it has combined the first name and then the last name without a space? Most of the time we want spacing. So we can fix that with our formula. We’re going to go adjust our formula a bit. With the cell reference article, I explained how we can use double quotes to symbolize a space in a formula like this: ” “.  Well, that’s exactly what we want.  Let’s try this again.

1. Go to the cell where you want to input your data. (Clear the data or adjust your formula based on the steps below.)

2. Type in the = sign here and type in the word CONCATENATE.

3. Type a ( sign or you can just press return on the keyboard.  This will start your formula.

4. Click on the cell that has the first name in it.  That cell address should now be in your formula and change color.

5. Type a comma

6. *NEW*  Type double quotes-space-double quotes ” “.

7. *NEW* Type a comma

8. Then click on the second piece of data, for example the last name.

9. Close your parenthesis or tap return on your keyboard.

Now, you should have a space between your names!  You can do this with more than two cells as well.  You can actually add whatever you like between the cells. We can go ahead and click and drag the formula down when we put the cursor on the lower right hand corner of the cell.

Faster formula copying

There is a really quick way to fill in the same formula instead of dragging a formula down, if your data is set up a certain way.  If you already have data in a column directly to the left or directly to the right we don’t need to click and drag.  Hover your mouse over the lower right corner to get the crosshairs. Double click the mouse and it will automatically fill the formula down. This is a kind of a neat little trick I use often. 

So there you go. You have your full names, they’re all combined, ready to go for whatever you might want to do.

One more thing: Resizing cells

You might notice when you combine names, for example, some of the names go over the “line” in your cells and go into the next cell.  You can click and drag to resize the cells by clicking on the line in the column headers between the letters.  A faster way to do this is just to double click on the line in the headers between the cells.  It will automatically adjust the column size to match the longest string, or data, in a cell.

So hopefully you find those two formulas, SPLIT and CONCATENATE, helpful for you.  Perhaps you know of more ways you can use this.  Let me know in the comments section below if you find other ways to make this work.  

Coming Up

In the next article I will be sharing a fun topic: drop down menus (data validation).  This is when you can choose from a list of items to put into a cell.  Setting this up correctly can help save time and also eliminate human error for data entry.

If there is something else you wish you knew how to do or would like me to explain, please contact me and let me know.  Also, if you are curious about some of the other work I do, you can check out the projects page on my site.  

Skip to content