Recipes / Recipe 15

Split a full name into first and last name in Excel or Google Sheets

Formulas for every version of Excel and for Google Sheets, including middle names, 'Last, First' lists and extra spaces.

FormulasCleanup Setup time: 2 minutes

You'll need: A column of full names (examples assume A2). Excel 365 or Excel 2024 for TEXTBEFORE/TEXTAFTER; the classic versions work in any Excel and in Sheets.

Excel 365 / 2024 (simplest)

First name (text before the first space)Formula

=TEXTBEFORE(TRIM(A2), " ", 1, , , TRIM(A2))

Last name (text after the last space)Formula

=TEXTAFTER(TRIM(A2), " ", -1, , , TRIM(A2))

Middle name(s), blank if noneFormula

=IFERROR(TEXTBEFORE(TEXTAFTER(TRIM(A2), " "), " ", -1), "")

Both at once, spilling into two cellsFormula

=HSTACK(TEXTBEFORE(TRIM(A2), " ", 1, , , TRIM(A2)), TEXTAFTER(TRIM(A2), " ", -1, , , TRIM(A2)))

The last argument (TRIM(A2)) is the “if not found” value, so a one-word name like Cher appears in both columns instead of showing #N/A.

Any version of Excel (and Google Sheets)

First nameFormula

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

Last name (last word)Formula

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))

The last-name trick swaps every space for 100 spaces, takes the right-most 100 characters (which can only contain the last word plus padding), and trims the padding.

Google Sheets

Split into one column per wordFormula

=SPLIT(TRIM(A2), " ")

First and last for a whole column at onceFormula

=ARRAYFORMULA(IF(A2:A="", , {REGEXEXTRACT(TRIM(A2:A), "^\S+"), REGEXEXTRACT(TRIM(A2:A), "\S+$")}))

Names written 'Last, First'

First name (Excel 365)Formula

=TRIM(TEXTAFTER(A2, ","))

Last name (any Excel or Sheets)Formula

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

Good to know

No formulas needed for a one-off: select the column, then Data → Text to Columns (Excel) or Data → Split text to columns (Sheets). Excel's Flash Fill (Ctrl+E) also works after you type two examples.
Surnames with spaces (van der Berg, de la Cruz) and suffixes (Jr.) can't be split reliably by any formula. Review those by hand, or have us build a rule list for your data.
Non-breaking spaces from web copy defeat TRIM. Clean them first with SUBSTITUTE(A2, CHAR(160), " "); see clean messy data.

Do it yourself

Recipe Club: $12/month

Prefer to set things up yourself? Join the Recipe Club and get access to a private GitHub repository of ready-made, tested automations for Sheets, Apps Script, Python and Node. Each one comes with setup notes and sample data.

A new recipe added every week
Everything in the free library, plus the longer, more complete versions
Use them in your own business as much as you like
Cancel anytime by email (or the Stripe customer portal once available)

Recipe Club opens soon: join the waitlist

You'll need a free GitHub account. After checkout, reply with your GitHub username and we'll send the repo invite, usually within one business day.

Or buy a one-time recipe pack ($19–$29)

Clean Data Pack $19
Dedupe, cleaning, merging and normalizing scripts and formulas for messy exports, with a sample sheet.
Coming soon
Forms & Inbox Pack $19
Form auto-replies, request routing, a lead log and follow-up nudges, ready to paste into Apps Script.
Coming soon
Money Ops Pack $29
Invoice reminders, a monthly revenue dashboard with QUERY, a receipts log and nightly backups.
Coming soon

Written with AI assistance. Script logic is unit-tested; formulas follow documented Google Sheets behavior. Always try new automations on a copy of your sheet first.