How to Split Names in Google Sheets

How to Split Names in Google Sheets

Put =SPLIT(A2, " ") in the empty column beside your names. The first name lands in that cell and the last name in the one to its right, and your original column stays exactly as it was. That single formula handles a list of clean two-word names completely.

Everything else on this page exists because real contact lists are not clean. Before you pick a method, scan your column for the four things that break the simple answer: middle names, compound surnames like "van Gogh", suffixes like "Jr.", and rows that arrived as "Smith, Jane" instead of "Jane Smith". Which of those you have decides the method.

One habit saves the most grief: insert two empty columns to the right of your names before you start. Both SPLIT and the Data menu need somewhere to put the result, and the menu command will overwrite whatever is already sitting there without asking.

How to Separate First and Last Name in Google Sheets Into Two Labeled Columns

SPLIT is a spilling formula. It writes into a cell you did not type in, which means the second column has no formula of its own and no header logic. If you want two properly separate columns that each own their result, write one formula per column instead.

Label B1 "First Name" and C1 "Last Name", then put these in B2 and C2:

Each column is now independent. You can sort by either one, delete one without disturbing the other, and edit the last-name formula to handle particles without touching the first-name formula. The dollar sign on $A2 keeps both pointing at the name column if you drag sideways.

This is also the version to use when the split feeds a mail merge or a CRM import, because those tools read a named column and will not follow a spill range.

Method 1: Data > Split Text to Columns

This is the fastest route and it needs no formula at all.

1. Select the column holding your full names.
2. Open the Data menu and choose Split text to columns.
3. A small dropdown appears at the bottom right. Set the separator to Space.

Sheets fills the columns to the right immediately. There is one catch that costs people real data.

This method is destructive. It replaces your original column in place, and it overwrites whatever already sits in the columns to the right without asking. Insert two blank columns first. If you want to keep the original name column intact, use a formula method instead.

Method 2: The SPLIT Function

SPLIT does the same job non-destructively. Put this in cell B2 and leave column C empty.

"Jane Smith" spills into B2 and C2. Your original column stays untouched, and the results update automatically when the source changes.

SPLIT breaks at every space. "Mary Jane Watson" produces three columns, not two. That shifts your data and misaligns every downstream formula. Method 5 handles that case properly.

Method 3: Pull Only One Part with INDEX

Often you only want the first name, for an email greeting. Wrap SPLIT in INDEX and ask for a specific position.

The last two arguments are row and column. Asking for column 2 returns the second word.

This keeps everything in a single cell, so nothing spills sideways into your other columns.

Method 4: LEFT, RIGHT, and FIND

This is the classic answer you will see in most older tutorials. It grabs everything before the first space.

Then everything after that space.

It works, but it is the most fragile option on this page. A single name with no space returns #VALUE!, because FIND cannot locate a space. Wrap it in IFERROR to fall back to the original cell.

Prefer Method 5 for anything beyond a strictly two-word list.

Method 5: REGEXEXTRACT for Middle Names and Suffixes

This is the method that survives real contact data. Instead of splitting at every space, take the first word and the last word specifically.

First name, meaning the first run of non-space characters:

Last name, meaning the final run of non-space characters:

"Mary Jane Watson" now correctly returns "Mary" and "Watson". The middle name is ignored rather than shifting your columns. The TRIM guards against leading and trailing spaces, which are extremely common in imported data.

Keeping a compound surname together. "Vincent van Gogh" should give "van Gogh", not "Gogh". Take everything after the first word instead of only the last word.

Note that these two surname rules are in direct conflict. The last-word rule breaks "van Gogh". The everything-after-the-first-word rule breaks "Mary Jane Watson" by returning "Jane Watson". No single regex resolves both, because the correct answer depends on knowing that "van" is a surname particle and "Jane" is a given name. That is knowledge about names, not a pattern in the text.

Stripping titles and suffixes first. If your data has "Dr." at the front or "Jr." at the end, remove them before splitting.

The (?i) flag makes the match case insensitive. Google Sheets uses the RE2 regex engine, which supports that flag but does not support lookaheads or lookbehinds. If you copied a pattern from a Stack Overflow answer written for Excel VBA or Python and it throws an error, a lookaround is the usual reason.

Method 6: Smart Fill

Google Sheets can infer the pattern from examples you type by hand.

1. In the column beside your names, type the first name for row 2 manually.
2. Type the first name for row 3.
3. Sheets usually offers a greyed-out suggestion for the rest of the column. Press Ctrl+Shift+Y to accept it, or click the checkmark on the suggestion chip. On a Mac it is Cmd+Shift+Y.

Ctrl+Enter does something different and is worth not confusing with this. It fills a selected range with the formula from the active cell. It does not accept a Smart Fill suggestion.

Smart Fill is genuinely convenient for a one-off cleanup. It is worth knowing its limit: it infers a single positional pattern from your examples. It will not reliably learn "keep compound surnames together but drop middle names", because that rule is not positional. Check the output rather than assuming it generalized correctly.

Method 7: Split the Whole Column at Once

Dragging a formula down a thousand rows is unnecessary. ARRAYFORMULA applies one formula to an entire range, and it picks up new rows as they arrive.

The IFERROR suppresses errors on the blank rows below your data. For the regex approach across a whole column:

Put this in a single cell at the top of an empty column. Everything below it must be empty, or you will get a #REF! error telling you the array result would overwrite data.

Handling "Last, First" Order

Exports from CRMs and school systems often arrive as "Smith, Jane". Here the separator is a comma, not a space.

To flip the name into normal "Jane Smith" order inside one cell, capture both halves and swap them.

Watch the delimiter you pass. It is tempting to write =SPLIT(A2, ", ") to match the comma and the space together. SPLIT's third argument, split_by_each, defaults to TRUE, which means Sheets splits on the comma and on the space independently rather than on the two-character sequence. "Smith, Jane" survives that, but "Van Halen, Eddie" comes apart into three columns.

Passing FALSE treats ", " as one delimiter and leaves the space inside the surname alone. The fourth argument, remove_empty_text, also defaults to TRUE, which is why two adjacent delimiters silently renumber your columns instead of leaving a blank one.

The real difficulty appears when a single column mixes both conventions, with some rows as "Smith, Jane" and others as "Jane Smith". No fixed formula handles both, because the position of the surname is not consistent.

How to Combine First and Last Name in Google Sheets

Going the other direction, with the first name in B2 and the last name in C2, use TEXTJOIN rather than the ampersand.

The signature is TEXTJOIN(delimiter, ignore_empty, text1, ...), and that middle TRUE is the whole reason to prefer it. It drops empty cells from the join instead of putting a delimiter around them.

Why that matters. If B2 holds "Jane", C2 is a blank middle name, and D2 holds "Smith", then =B2&" "&C2&" "&D2 returns "Jane Smith" with two spaces. The lookup you build on that column later will not match. TEXTJOIN with TRUE returns "Jane Smith" with one space. Merging first and last name and combining them are the same operation, and this is the version that survives incomplete rows.

Skip =CONCATENATE(B2:D2) here. It does accept a range, but it joins with no separator at all and gives you "JaneSmith".

Merging names into "Last, First" order

For an alphabetized directory you usually want the surname first.

Combining a whole column of names at once

The obvious ARRAYFORMULA version has a trap in it.

That combines your names correctly, and then it keeps going. Every empty row below your data receives a single space, so those cells are no longer blank. COUNTA jumps to tens of thousands, the sheet looks like it has data to the bottom, and anything that stops at the last non-empty row now stops in the wrong place. Guard the blanks explicitly.

TEXTJOIN cannot be used this way. It is an aggregating function, so inside ARRAYFORMULA it collapses the entire range into one string in one cell rather than working row by row. When you want TEXTJOIN's blank handling applied per row, use BYROW instead.

The inner JOIN check is what keeps the empty rows empty. For joining fields that are not names, such as building a mailing label or an SKU, see combining text from multiple cells in Google Sheets.

How to Sort by Last Name in Google Sheets

You do not need a helper column. SORT accepts an array as its sort_column argument, not only a column number, so you can compute the surnames inside the sort itself.

BYROW produces a one-column array of last names that lines up row for row with the range being sorted. SORT orders by that array and returns your original columns untouched. Nothing extra appears on the sheet.

If you have already split the names into separate columns, the plain form is enough. With full names in A, first names in B, and last names in C:

The 3 is the column's position inside the range, not its letter on the sheet. TRUE sorts A to Z. If your surnames are a mix of "MacDonald" and "macdonald", sort on LOWER() of the column so case does not scatter them, or normalize the capitalization first with the next section.

How to Fix Name Capitalization After Splitting

Imported lists arrive as "JANE SMITH" or "jane smith" more often than not. PROPER capitalizes the first letter of every word and lowercases everything else.

Two behaviours will surprise you, and both come from the same rule. PROPER treats every non-letter as a word boundary.

• "MCDONALD" becomes "Mcdonald", not "McDonald". PROPER has no way to know that the second capital belongs there.
• "O'BRIEN" becomes "O'Brien", which is correct, but by the same rule "SMITH'S" becomes "Smith'S".

Fixing those means knowing which strings are surnames with internal capitals and which are possessives, which is knowledge about names rather than a pattern in the characters. That is the same wall the split formulas hit, and the next section is where it gets handled.

How to Reuse Your Split Formula With a Named Function

Once you have settled on a regex that handles your data, you do not have to paste it into every new sheet. Google Sheets has supported named functions since 2022, and they take arguments the same way built-ins do.

1. Open Data and choose Named functions.
2. Click Add new function. Name it LASTNAME and add one argument called full_name.
3. Enter the definition =IFERROR(REGEXEXTRACT(TRIM(full_name), "(\S+)$"), full_name).

Now =LASTNAME(A2) works anywhere in the file, and it reads like what it does. The same dialog imports functions from another spreadsheet, so a rule you get right once travels to the next project.

The catch is scope. A named function belongs to the spreadsheet you defined it in, not to your account. Anyone you share a file with can use the functions inside it, but a colleague opening a fresh sheet will see #NAME? until they import it too.

When Formulas Stop Being Worth It

Every method above is a positional rule. Take the first word. Take the last word. Split at the comma. Those rules work because most names happen to fit them.

The cases that break them are the ones where the correct split requires knowing something about names themselves. That is where FITS helps, by letting you describe the outcome instead of encoding a pattern.

For a column that mixes "Smith, Jane" and "Jane Smith" rows, state the normalization once.

The capitalization problem from the PROPER section resolves the same way, because "McDonald keeps its inner capital but Smith's does not" is a fact about names rather than a pattern in the letters.

Two honest caveats. This calls an AI model, so it is slower per cell than a native formula and it is not free at large volumes. It is also not deterministic in the way REGEXEXTRACT is.

So do not reach for it on a clean two-word list. Use SPLIT or Text to Columns when your data is uniform. Reach for FITS when your list is genuinely messy and the alternative is hand-correcting rows.

Which Method Should You Use

Troubleshooting

Formula parse error on a comma. If your Google account uses a European locale, formula arguments are separated by semicolons. Write =SPLIT(A2; " ") instead.

#REF! saying the result would overwrite data. Your SPLIT or ARRAYFORMULA needs empty cells to expand into. Clear the cells to the right and below.

#VALUE! from a LEFT or FIND formula. That row has no space in it, usually a mononym or a blank. Wrap the formula in IFERROR.

Names look right but lookups still fail. Invisible whitespace is the usual culprit, especially non-breaking spaces from pasted web content. See removing extra spaces in Google Sheets for the fix that TRIM alone misses.

Related Guides

Splitting names is one flavor of a broader problem. For fields separated by commas, pipes, or slashes, see splitting text by delimiter. Going the other direction, to rebuild a full name or a mailing label from parts, is covered in how to combine text from multiple cells in Google Sheets. When all you have is a work address, you can pull a real name out of an email address, and a mailing label breaks apart the same way in parsing an address into street, city, state, and ZIP. A contact record usually hides a phone number too, so see how to extract a phone number when the format varies row to row. When the same person appears under three spellings, grouping similar text gives every variant one canonical name. For the wider picture, see how to automate the Google Sheets tasks you used to need regex for and the definitive guide to AI data cleaning.

Frequently Asked Questions