A full name in one cell is awkward to sort, mail-merge or match. This page splits names into first, middle and last with the built-in tool and with formulas, then handles the cases that break simple splits: "Last, First" order, titles and stray spaces.
Names in A2 down, row 1 holds headers:
Mary Jane Smith
John Smith
Cher
Smith, John
Dr. Jane Brown
Select the column, then choose Data > Split text to columns. A separator box appears; pick Space (or Detect automatically). The names split into columns in place and overwrite whatever is to the right, so insert empty columns first. It is a one-off action: if the source changes, nothing updates. Use a formula when the data keeps changing.
=SPLIT(TRIM(A2)," ")
For Mary Jane Smith this spills three cells: Mary, Jane, Smith. For John Smith it spills two. Rows with different numbers of words give different numbers of cells, so the last name is not always in the same column. That is the reason for the next method. SPLIT drops empty pieces by default, so doubled spaces do not create blank cells, but keep the TRIM, because other functions below do not.
First name:
=REGEXEXTRACT(TRIM(A2),"^\S+")
or without regex:
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)
Both return Mary for Mary Jane Smith, John for John Smith and Cher for Cher. The extra " " added inside FIND stops a one-word name from returning an error.
Last name is the final word:
=REGEXEXTRACT(TRIM(A2),"\S+$")
This gives Smith, Smith and Cher. A single name counts as both first and last. If you want the last name blank for one-word names, wrap it: =IF(ISERROR(FIND(" ",TRIM(A2))),"",REGEXEXTRACT(TRIM(A2),"\S+$")).
With first name in B2 and last name in D2, the middle is whatever remains:
=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2)-LEN(D2)))
Mary Jane Smith returns Jane. John Smith returns an empty cell. Several middle names stay together as one string ("Mary Anne Jane Smith" gives "Anne Jane"). This assumes A2 has no extra spaces, so run Method 3 on a cleaned copy.
For "Smith, John", split on the comma and trim each piece:
=ARRAYFORMULA(TRIM(SPLIT(A5,",")))
This spills Smith then John. To flip it into a normal "John Smith" in one cell:
=TRIM(REGEXREPLACE(A5,"^([^,]+),\s*(.*)$","$2 $1"))
This returns John Smith. You can then use Method 3 on the result.
Strip a leading title before splitting:
=TRIM(REGEXREPLACE(A6,"(?i)^(Mr|Mrs|Ms|Miss|Dr|Prof)\.?\s+",""))
Dr. Jane Brown becomes Jane Brown. The \s+ after the title means a name that merely starts with those letters, such as Drew Barry, is left alone. Suffixes such as Jr or III are not handled here; strip them with a similar REGEXREPLACE at the end of the pattern if you have many.
Leading, trailing and doubled spaces break LEFT, FIND and MID. TRIM removes all of them, and Data > Data cleanup > Trim whitespace does the same to the cells themselves. Text copied from web pages may contain non-breaking spaces (character 160), which TRIM leaves alone. Swap them first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Compound surnames such as "van der Berg" or "De la Cruz" will come out as just the last word. Check them by hand or keep a small exceptions list.
Names with only initials ("J. R. Smith") work, but the first name is "J.".
Hyphenated names count as one word.
No formula can tell which word is the surname in every culture. Treat the output as a first pass and eyeball the odd ones.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Splitting needs nothing beyond the formulas above. If you later need to match the split names against another list where spelling differs, see https://www.snakbacks.com/match-names-with-typos-google-sheets
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.