How to Separate First and Last Name in Google Sheets

If you work with Google Sheet and get a spreadsheet with first and last names combined, you know you cannot sort by last names. Sorting by the field with both first and last combined is of no use. In these cases you need to separate the first and last names prior to sorting.

There are many different ways to split a column of full names into separate columns in Google Sheets. Let’s look into two simple and efficient methods that you can use.

Use the Split Text into Columns Tool

Here is the most straightforward way to split full names into different columns using Google Sheets tools.

1. Create a Copy of the Full Name Column: This tool will alter the names in the column you apply it to. If you want to keep the initial names intact, you should apply the tool to a copy of the original column. You can copy-paste the column of full names, or use a formula to duplicate them.

2. Select the New Column
3. At the Top, Select Data
4. Click on “Split text into columns…”

This option automatically splits up your data. But before that happens, you have to choose a Separator.

5. Choose Separator: Space

You want to split the names up along the spaces between the first and last names.

Each part of the full name goes into a separate column. What are the situations when this method is useful?

• If You Only Need First Names : The first column is your First Name column.

• If There Are No Middle Names : If every name on your roster consists of just a first name and a last name, this method splits them neatly into two.

• If the Full Names are Separated by Comma

Instead of Separator: Space, there are other options available. There is a custom option as well. So if the names in your Full Name column are separated by a comma, semicolon, or any other symbol, you can use this method to split them apart at the symbol.

Simply repeat the steps above, but select Separator: Comma. You can also try Separator: Detect automatically. The number of your resulting columns will depend on the number of commas used in each full name.