How To Separate First And Last Name In Google Sheets

Last Updated on November 16, 2023 by Jake Sheridan

In this tutorial, you will learn how to separate first and last name in Google Sheets.

It’s very common to have a list of full names in Google Sheets and need to separate them into first and last names. For example, if you need to do mail merge or upload data to a database. For short lists you could manually edit each name, but many such lists can be extremely long so that’s not always feasible.

Fortunately, it’s easy to automatically separate first and last names in Google Sheets. Below are a couple different methods for splitting names.

Separate First and Last Name Using the SPLIT Function

The simplest method to split names in Google Sheets is to use the SPLIT function. The benefit to using this approach is that the formulas will be live and recalculate any time you update the name list. The downside of course is that the resulting names are formulas so cannot be edited directly and will disappear if you delete the original list of full names

The SPLIT function splits text into columns on a specified character, or delimiter. In this case we’ll use a space as the delimiter since first and last names are typically separated by a space, but you can use whatever character or characters you need.

Here’s how to use the SPLIT function to split names:

Step 1

Select the cell to put the SPLIT formula in. Be sure the following column is blank to allow room for the last name to populate

Step 2

Type the following formula: “=SPLIT(A2, ” “)”. In this formula, A2 is the reference to the name to be split, and the quotes around a space specify that the name should be split on the space

Step 3

Hit enter and the first and last name will populate in separate columns

Step 4

Drag the formula down or copy paste it down to cover the entire list

Step 5

You can also use an array formula to split all names in the column with a single formula. If the list of names is in column A, here’s the formula you should use: “=ARRAYFORMULA(SPLIT(A2:A, ” “))”. Be sure there are enough blank cells following this formula for it to expand fully

Separate First and Last Name Using the Menu Option

If you don’t want to use formulas to separate first and last names, you can also use the menu option.

Here’s how:

Step 1

Select the range you want to split into first and last names

Step 2

Open the Data menu, and select the Split Text To Columns option

Step 3

A separator selection menu will appear floating over the cells

Step 4

Choose Space from the Separator menu

Step 5

The names in the selected range will be split into first and last names

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to separate first and last name in Google Sheets. Want more? Check out all the Google Sheets Tutorials.