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.