Skip to content
CSVTidy

How to Split a Full Name Column into First and Last Name

4 min read

Excel formulas, Flash Fill, Google Sheets, and Python for splitting name columns — plus the middle names, suffixes, and compound surnames that break naive approaches.

Almost every CRM, email platform, and event tool wants First Name and Last Name as separate fields. Almost every export you receive gives you a single Full Name column.

Splitting it is a two-minute job for clean data and a genuinely hard problem for messy data. Here's both.

Excel: Flash Fill (easiest)

Excel can infer the pattern from a couple of examples.

  1. In the column beside your names, type the first name from row 1 manually.
  2. Start typing the first name from row 2 — Excel shows a grey preview of the whole column.
  3. Press Enter to accept.
  4. Repeat in the next column for last names.

Flash Fill (Ctrl+E) is fast and handles most simple cases. Verify the output, though — it infers patterns and can guess wrong partway down a column without telling you.

Excel: formulas

More predictable than Flash Fill, and they update when the source changes.

First name (everything before the first space):

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

Last name (everything after the last space):

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

That second one looks bizarre. It replaces every space with 100 spaces, then grabs the final 100 characters and trims — a reliable trick for "everything after the last space" that handles middle names correctly.

Both formulas break on single-word entries. Wrap them:

=IFERROR(LEFT(A2, FIND(" ", A2) - 1), A2)

Google Sheets: SPLIT

=SPLIT(A2, " ")

Spills across columns — one per word. Fine when every name is exactly two words, unusable when some have three. For consistent two-column output, use the same LEFT/FIND and RIGHT/SUBSTITUTE formulas as Excel; they work identically in Sheets.

There's also Data → Split text to columns, which does the same as SPLIT through the menu.

Python: pandas

import pandas as pd

df = pd.read_csv("contacts.csv")
df["Full Name"] = df["Full Name"].str.strip()

# Split on the LAST space: everything before is first, after is last.
parts = df["Full Name"].str.rsplit(" ", n=1, expand=True)
df["First Name"] = parts[0].fillna(df["Full Name"])
df["Last Name"] = parts[1].fillna("")

df.to_csv("contacts-split.csv", index=False)

Using rsplit with n=1 puts middle names into the first-name field, which matches what most CRMs expect.

The cases that break simple splits

This is where naive approaches quietly produce garbage. Decide how you want each handled before you run anything.

| Input | Naive split gives | Usually correct | |---|---|---| | Jane Marie Smith | First: Jane, Last: Marie Smith | First: Jane Marie, Last: Smith | | Doe, John | First: Doe,, Last: John | First: John, Last: Doe | | Dr. Jane Smith | First: Dr., Last: Smith | First: Jane, Last: Smith | | John Doe Jr. | First: John, Last: Jr. | First: John, Last: Doe | | Maria van der Berg | First: Maria van der, Last: Berg | First: Maria, Last: van der Berg | | Cher | Error or blank last | First: Cher, Last: empty |

The first four are solvable with rules: split on the last space, detect a trailing comma in the first token, strip a known list of honorifics (Mr, Mrs, Ms, Dr, Prof) and suffixes (Jr, Sr, II, III, PhD, MD).

Compound surnames like van der Berg, de la Cruz, and O'Brien-Smith are harder, because van and de are also legitimate given names in some cultures. There is no rule that gets these right every time.

Accept that you can't automate 100%

Names are not a solved problem. Mononyms, patronymics, cultures where the family name comes first, and people whose legal name simply doesn't fit a two-field model all exist in real data.

The practical approach:

  1. Run an automated split to handle the 95% that are straightforward.
  2. Sort by last name and scan the top and bottom — errors cluster at the extremes (blanks, single characters, unusually long values).
  3. Fix the handful that are wrong by hand.
  4. Keep the original Full Name column. When someone reports their name is wrong in an email, you'll want the source value.

That last point matters more than any formula on this page. Getting someone's name wrong in a mail merge is a small, avoidable insult — and the original column is your ability to check.

Checklist

  • Keep the original column.
  • Decide where middle names go before you start.
  • Handle Last, First entries separately — they need reversing, not splitting.
  • Strip honorifics and suffixes.
  • Manually review compound surnames and single-word names.

Keep reading