How to Separate Names in Google Sheets

Would you like to know how to separate names in Google Sheets?

Last updated: August 24, 2026

Whether organizing a contact list, preparing personalized emails, or tidying up your data, separating names can make your life much easier. You don't need to be a spreadsheet expert to split names in Excel and Google Sheets.

This guide covers every way to split a full name in Google Sheets, from the one-formula answer to the awkward cases: middle names, two-word surnames, data typed as "Lamar, James", and lists that keep growing.

What this guide covers

• Which method should you use?
• Why separate names in Google Sheets?
• How do you split names with the SPLIT function?
• How do you separate names with Split text to columns?
• How do you extract first, middle, and last names with formulas?
• How do you split names into two columns?
• How do you separate a name written last name first?
• How do you split a name and surname with more than one word?
• How do you sort or alphabetize by last name?
• Troubleshooting: errors and odd results
• Frequently asked questions

Which method should you use?

All four methods below split a name. They differ in one thing that matters more than anything else: whether the split keeps working after you add more names.

The rule: if the list is finished, use Split text to columns. If the list is still being added to, use a formula. Split text to columns runs once and never runs again.

Why separate names in Google Sheets?

Separating names in Google Sheets can be incredibly useful for various tasks. Below, we will explain why you should separate names and how it can make your work easier and more efficient:

1. Easier sorting and filtering.

When first and last names are separated into individual columns (like first, middle, and last names), sorting and filtering your data becomes much simpler. For example, if you want to organize your list alphabetically by last name, having a dedicated column for last names allows you to do this quickly. This makes managing large datasets more manageable and ensures that your information is always well-organized.

2. Personalized communication.

Separating names can also enhance personalized communication. If you send out emails or letters, having the first names in a separate column allows you to personally address recipients. For instance, using a mail merge feature, you can automatically insert first names into your email greetings, making your messages more personal and engaging.

3. Better data organization.

Overall, separating names leads to better data organization. When each part of a name has its column, you reduce the risk of errors and inconsistencies. It becomes easier to analyze your data and create detailed reports. For example, you can quickly generate statistics about your dataset's most common first or last names or filter out duplicates more effectively.

Read more: How to split cells in Google Sheets.

How do you split names with the SPLIT function?

The SPLIT function is the quickest way to break a full name into its parts, and it is the only method that keeps working as your list grows. Here's how to do it:

Step 1: Select the cell.

First, click the cell where you want the first part of the separated name to appear. This is where the SPLIT function will start placing the split parts of the name.

Step 2: Enter the SPLIT function.

In the selected cell, type the following formula:

=SPLIT(A1, " ")

• A1 represents the cell containing the full name you want to split.

• The " " inside the function is a single space. It tells Google Sheets that a space is what separates one part of the name from the next.

Step 3: Press enter.

Once you've entered the SPLIT function, press Enter. Google Sheets will automatically separate the names and distribute them into different columns. For example, if you have "James Brown" in cell A1, the first name "James" will appear in the selected cell, and "Brown" will appear in the next cell to the right.

The rule: SPLIT writes across the row, not down it. One formula in one cell fills as many columns to the right as the name has parts, so every column it needs must be empty.

Google's own reference for the function, including the optional arguments, is the SPLIT function page in Google Docs Editors Help.

How do you separate names with Split text to columns?

Split text to columns is the menu version. It is faster than a formula for a list you have finished collecting, but it is a one-time action and it overwrites the column you run it on. Here's how to do it:

Step 1: Select the column.

Start by highlighting the column that contains the full names you want to separate. Click the letter at the top to select the entire column.

Step 2: Go to the Data menu.

Next, go to the top menu of Google Sheets and click the "Data" tab. This will open a drop-down menu with several options for managing your data.

Step 3: Choose Split text to columns.

From the drop-down menu, select Split text to columns. This option will split the contents of the selected column into separate columns based on a separator you choose.

Step 4: Choose the separator.

After selecting Split text to columns, a small Separator box appears at the bottom right of the highlighted column. Click it and choose Space to separate names. This tells Google Sheets to split the text at every space in the full names.

Before you run it, insert two blank columns to the right of your name column. Split text to columns writes the parts into the original column and the columns beside it, and it will overwrite whatever is already there without asking.

How do you extract first, middle, and last names with formulas?

Use these when you want one specific part of the name in its own column rather than all the parts at once. They also handle more advanced Google Sheets formula work such as names with middle initials.

Extracting the first name.

You can use the LEFT and FIND functions together to extract the first name from a full name.

Formula: =LEFT(A1, FIND(" ", A1) - 1)

• A1 is the cell with the full name.

• FIND(" ", A1) locates the position of the first space in the name.

• LEFT(A1, FIND(" ", A1) - 1) takes all characters to the left of the first space, giving you the first name.

Extracting the middle name (if any).

You can use the MID function along with FIND for names with a middle name or initial.

Formula: =MID(A1, FIND(" ", A1) + 1, FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1) - 1)

• FIND(" ", A1) + 1 finds the starting position of the middle name.

• FIND(" ", A1, FIND(" ", A1) + 1) finds the position of the next space after the first name.

• The MID function then extracts the middle name by taking the characters between the first and second spaces.

Extracting the last name.

To extract the last name from a three-part name, use the RIGHT, LEN, and FIND functions.

Formula: =RIGHT(A1, LEN(A1) - FIND(" ", A1, FIND(" ", A1) + 1))

• LEN(A1) gives the total length of the name.

• FIND(" ", A1, FIND(" ", A1) + 1) locates the position of the second space.

• RIGHT(A1, LEN(A1) - FIND(" ", A1, FIND(" ", A1) + 1)) extracts the last name by taking all characters to the right of the second space.

Last name that works on both two-part and three-part names (takes everything after the first space):

=TRIM(RIGHT(A1, LEN(A1) - FIND(" ", A1)))

"James Brown" returns "Brown". "James Michael Lamar" returns "Michael Lamar".

Last word only, whatever the name looks like (this is the one to use if you want the surname and there may be middle names):

=REGEXEXTRACT(A1, "\S+$")

"James Brown" returns "Brown". "James Michael Lamar" returns "Lamar". The matching first-word version is =REGEXEXTRACT(A1, "^\S+").

Worked example

Suppose you have the name "James Michael Lamar" in cell A1:

• First name: =LEFT(A1, FIND(" ", A1) - 1) will give you "James".

• Middle name: =MID(A1, FIND(" ", A1) + 1, FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1) - 1) will give you "Michael".

• Last name: =RIGHT(A1, LEN(A1) - FIND(" ", A1, FIND(" ", A1) + 1)) will give you "Lamar".

How do you split names into two columns in Google Sheets?

This is the layout most people actually want: full names in column A, first names in column B, last names in column C.

1. Make sure columns B and C are empty. If they are not, right-click column B and insert two columns.

2. Click B2 and type =SPLIT(A2, " "), then press Enter. B2 fills with the first name and C2 fills with the last name.

3. Click B2 again, copy it, then select B3 down to the last row of your list and paste. Every row now splits into two columns.

4. Leave column C alone. It is filled by the formula in column B, not by a formula of its own.

The rule: you only ever put the formula in the first of the two columns. Typing anything into column C breaks the split and produces a #REF! error on that row.

If you would rather have two independent formulas, one per column, use =REGEXEXTRACT(A2, "^\S+") in B2 and =REGEXEXTRACT(A2, "\S+$") in C2. Those two do not depend on each other, so you can fill both columns down safely.

How do you separate a name written last name first, like "Lamar, James"?

Exported contact lists and school rosters usually store names in last-name-first order with a comma. A space split will not help you here, because the comma is the real separator and the parts arrive in the wrong order.

With "Lamar, James" in A2:

• Last name: =TRIM(LEFT(A2, FIND(",", A2) - 1)) returns "Lamar".

• First name: =TRIM(RIGHT(A2, LEN(A2) - FIND(",", A2))) returns "James".

The TRIM is not optional. Without it the first name comes back with a leading space, which quietly breaks lookups and mail merges later.

To do both at once, =SPLIT(A2, ", ") splits on the comma and the space together and drops the empty piece between them, giving you "Lamar" in one cell and "James" in the next.

To flip the whole thing back into normal reading order in a single cell:

=TRIM(RIGHT(A2, LEN(A2) - FIND(",", A2))) & " " & TRIM(LEFT(A2, FIND(",", A2) - 1))

That returns "James Lamar".

How do you split a name and surname when the surname has more than one word?

Names like "Ana van der Berg" or "Maria de la Cruz" defeat every space-based split, because the space between "van" and "der" is not a boundary between name parts.

Split on the first space only:

• First name (given name): =TRIM(LEFT(A2, FIND(" ", A2) - 1))

• Surname (everything after it): =TRIM(RIGHT(A2, LEN(A2) - FIND(" ", A2)))

"Ana van der Berg" returns "Ana" and "van der Berg". That is correct for multi-word surnames and wrong for names with a middle name, where it would return "Michael Lamar" as the surname.

The honest rule: no formula can tell a middle name apart from a two-word surname, because there is no signal in the text that distinguishes them. Pick the rule that fits the majority of your list, apply it to everything, then scan the results and fix the handful of exceptions by hand.

The same problem applies to suffixes. "Robert Chen Jr." will hand you "Jr." as the surname if you take the last word. If your list has suffixes, take everything after the first space instead and clean up separately.

How do you sort or alphabetize a Google Sheet by last name?

The rule: you cannot sort by last name while the whole name sits in one cell. Google Sheets sorts on the first character in the cell, which is the first letter of the first name. Splitting the name is the prerequisite, not an optional extra.

Once you have a last name column (say column C):

1. Select the full block of data including headers, for example A1:C500.

2. Go to Data and then Sort range and then Advanced range sorting options.

3. Tick Data has header row.

4. Choose your last name column in the "Sort by" list and pick A to Z.

To keep an always-sorted copy on another tab without touching the original, use =SORT(A2:C, 3, TRUE). The 3 is the third column of the range, which is the last name column, and TRUE means ascending.

Troubleshooting: errors and odd results

Conclusion

Separating names in Google Sheets comes down to one decision: is this list finished or is it still growing? A finished list is fastest with Split text to columns. A growing list needs =SPLIT(A2, " ") so the split keeps up. Everything else on this page is a variation for the awkward names in the middle of your data.

Visit Simple Sheets for more easy-to-follow guides and examples, and remember to visit the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions

What is the easiest way to split names in Google Sheets?

Type =SPLIT(A2, " ") in the cell to the right of the full name and press Enter. The first name stays in that cell and the last name spills into the next column. Copy the formula down the column to split the whole list.

How do I split names into two columns in Google Sheets?

Put =SPLIT(A2, " ") in B2 and leave column C empty. B2 receives the first name and C2 receives the last name. SPLIT writes across the row, so any column it needs to the right must be free.

What if commas or other characters separate my names?

Use the SPLIT function with that character as the delimiter. =SPLIT(A2, ",") splits on a comma. =SPLIT(A2, ", ") splits on the comma and the space together, which is what you want for data written as Lamar, James.

How do I handle names with multiple spaces?

Wrap the cell in TRIM first. =SPLIT(TRIM(A2), " ") removes leading, trailing, and repeated spaces before the split runs, so you do not get blank columns between the name parts.

Why does my last name formula return #VALUE! in Google Sheets?

Because the formula looks for a second space and the name only has one. A formula built on FIND(" ", A2, FIND(" ", A2) + 1) errors on a two-word name like James Brown. Use =TRIM(RIGHT(A2, LEN(A2) - FIND(" ", A2))) instead, which takes everything after the first space.

Does Split text to columns update automatically when I add new names?

No. Split text to columns is a one-time action. It splits what is in the column at the moment you run it, overwrites the original column, and does nothing to names you type afterwards. Use the SPLIT formula if the list keeps growing.

How do I split a name and surname in Google Sheets when the surname has two words?

Split on the first space only. =TRIM(LEFT(A2, FIND(" ", A2) - 1)) gives the first name and =TRIM(RIGHT(A2, LEN(A2) - FIND(" ", A2))) gives everything after it, so Ana van der Berg returns Ana and van der Berg.

Related Articles

How to Split Cells in Google Sheets

Google Sheets Formulas

How to Add Cells in Excel

How to Copy Formula in Excel

How to Autofit in Excel