How to split first and last names in Google Sheets

By Jake Marsh · Updated

To split first and last names in Google Sheets, select the column of full names, choose Extensions > Cellwell > Split names, tick First name and Last name (and Middle name, Title, Suffix or Nickname if you want them), click Preview, then Apply. Cellwell inserts the new columns next to the names and fills them, reading "Last, First" order, titles like Dr. and suffixes like Jr. correctly.

Why Split text to columns gets names wrong

Sheets' Data > Split text to columns cuts at every space. That works for "Ana Ruiz", but not for most real lists:

Full nameSplit at spacesCellwell
Dr. Maria de la CruzDr. / Maria / de / la / CruzTitle Dr., First Maria, Last de la Cruz
Smith, John A.Smith, / John / A.First John, Middle A., Last Smith
Robert "Bob" Lee Jr.Robert / "Bob" / Lee / Jr.First Robert, Last Lee, Suffix Jr., Nickname Bob
Vincent van GoghVincent / van / GoghFirst Vincent, Last van Gogh

When every row has a different number of words, the space-split columns no longer line up, and fixing them by hand takes longer than the split did.

Steps

  1. Select the names. Click the column letter or the first name cell; Cellwell extends the selection down to the last row of data.
  2. Choose Extensions > Cellwell > Split names.
  3. Tick the New columns you want: Title, First name, Middle name, Last name, Suffix, Nickname. Only ticked parts get a column.
  4. Choose the options:
    • Keep middle names with the first name puts "Mary Ann" in First name instead of splitting it.
    • Fix names typed in ALL CAPS or lowercase turns "JOHN MCDONALD" into "John McDonald" and keeps O'Brien and van der Berg right.
    • Replace the original column puts the first part where the full names were, instead of keeping them.
  5. Click Preview and look at the rows. Each new column is shown with its values.
  6. Click Apply. Cellwell inserts the columns to the right of the names and fills them.

If a name comes out wrong, open History and Undo and undo the run, then change the options and run it again.

Before an import into a CRM or email tool

Most import screens want First name and Last name in separate columns. Run Split names with Fix names typed in ALL CAPS or lowercase ticked, then Clean text and spaces on the email column to strip stray spaces and invisible characters that make an import reject a row.

What it costs

Cellwell's trial is 30 days or 50 Apply runs, whichever comes first, and one Apply splits the whole selection. Then $15 a month, $50 a year, or $100 once. Previews and Undo are always free. Install from the Cellwell page.

Cellwell: Split Names, Merge, Change Case & Clean Text

Split names, split and merge text, change case, clean, extract and fill random data in Google Sheets, with a preview of every change and Undo $15 a month or $50 a year per Google account.


Questions

How do I separate names with a comma in Google Sheets?

Select them and run Split names. Cellwell reads "Smith, John" as last name Smith and first name John.

Can it split names into two columns only?

Yes. Tick only First name and Last name. Middle names go with the first name if you tick Keep middle names with the first name.

Does it handle names from other cultures?

It knows common particles such as van, von, de, de la, da and bin, and keeps them with the last name. Names without a clear first and last order, or where the family name comes first without a comma, may need a check in the preview.

Will it change my original column?

Only if you tick Replace the original column. Otherwise the full names stay where they are.


More Cellwell guides


More Google guides