Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Splitting a List of Names in a Google Spreadsheet

Splitting a List of Names in a Google Spreadsheet

Have you ever had a list of students first and last names and wish you had their first and last name separated? If you’ve asked for “name” on a Google Form this is a likely scenario for you. Note: I always ask for first and last name in 2 different questions. 

The formula in Google Spreadsheet to split up the text in a cell is =Split(

Example

Here is my list of names. (In this case I got it from http://listofrandomnames.com)

Gaylord Balcom
Hosea Jack
Vera Fuselier
Kacy Poplar
Scottie Wallen
Deeanna Soukup
Brant Dipaola
Jeffie Parnell
Monte Heilig
Terrell Metzer
Annelle Ziolkowski
Lucila Tiger
Marybelle Ensign
Carmen Caylor
Coretta Harriss
Melida Waiters
Darlene Mund
Minta Lofton
Roxie Galyean
Theressa Hoaglin

Split

In this case I want to split the first name up from the last name into different cells. In Excel I would use text to columns but that is not an option in Google Spreadsheet.

First, make sure you have enough blank columns to split up the names. If someone has 2 last names or a middle name or middle initial you will need a column per. Otherwise the =split( ) function WILL overwrite your data in adjacent columns.

Step 1

If necessary, right click on the column to insert a blank column.

Hint: if you highlight multiple columns and then right click you can insert multiple columns at once.

Step 2

If your list of names is in column A, you should now have columns B and C inserted as blank columns if they were not already blank.

In the blank cell next to the cell with a name in it, type =split(A2,” “).
Note that “A2” refers to the cell that your text is in, adjust accordingly.

Notice that after the comma is a quotation, a space bar, and a quotation. You are telling Google Spreadsheet to SPLIT the name up at the spacebar. Not that what you tell Google Spreadsheet to split at, will be “eaten.”

Step 3

Drag and fill down the split formula. You do not need to write the formula multiple times.

Step 4

While it looks like the first and last names are in columns B and C, really it is the formula. It is probably fine to leave it, but can cause some complications depending on what you want to do with the list.

Highlight the columns containing the split up information. In this example, columns B and C.

Command C on a Mac, Control C on a PC, to copy the cells.

Right click on the first first name and choose
Paste special
Paste values only

This will override the formula and make the names permanently text.

Step 5

Delete column A by right clicking on the column identifier and choosing delete.

YouTube video

11 thoughts on “Splitting a List of Names in a Google Spreadsheet

  1. I never leave comments on these things, but this was the most helpful google spreadsheet tutorial I have ever read! I search for formulas and tutorials all the time, and they are rarely as easy to follow as this, and usually I have to sift through tons of reading to find what I’m actually looking for. This was quick, easy, and actually what I was trying to do! Thank you!!!!!

  2. Just what I was looking for. Short and to the point, but well explained. I followed your instructions and got my Google Sheet formatted the way I wanted in two minutes. Thanks so much!

  3. As Molly said, I do not leave comments, but this was so helpful I figured I would let you know. This was exactly what I needed, and perfectly explained. Thank you.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.