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:
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
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
Hit enter and the first and last name will populate in separate columns
Drag the formula down or copy paste it down to cover the entire list
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.
Select the range you want to split into first and last names
Open the Data menu, and select the Split Text To Columns option
A separator selection menu will appear floating over the cells
Choose Space from the Separator menu
The names in the selected range will be split into first and last names
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.
More ways to clean data in Google Sheets:
- Clear Cells
- Clear Contents
- Clear Formatting
- Clear Filter
- Stop Google Sheets From Rounding
- Remove Dollar Sign
- Stop Google Sheets From Deleting Zeros
- Format Phone Numbers
- Change Currency
- Add Dollar Sign
- Split Text To Columns
- Split Text to Rows
- Split Cells
- Split Text
- Find And Replace
- Find And Replace Wildcard
- Swap Cells
- Check if a Cell is Not Empty