Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Split Full Names into First and Last Names with pandas

A single "Name" column holds "Ada Lovelace", "Lovelace, Ada", "Dr. Grace Hopper", "Ludwig van Beethoven" and "Smith Jr., John". Split it into first and last names with rules for commas, titles, suffixes and surname particles, keep the original, and flag the names no rule should guess about.
Data cleaningpandasExcel functions in pandas

A contact export with one Name column is fine until someone needs "Dear Ada" in a mail merge, a sort by surname, or a match against a system that stores first and last names separately. Excel users reach for Text to Columns, pandas users for str.split, and both get "Ada Lovelace" right and "Ludwig van Beethoven" wrong. Names are messy in predictable ways — reversed order with a comma, titles and suffixes, surname particles, extra spaces, all capitals — and in some ways no rule can predict. This page, part of Cleaning Excel Data With Pandas, handles the predictable cases with explicit rules and flags the rest.

Full names split into first and last Left: a name column with Ada Lovelace, Lovelace comma Ada, Dr. Grace Hopper, Ludwig van Beethoven and Smith Jr. comma John. Right: first and last columns with Ada Lovelace, Ada Lovelace, Grace Hopper, Ludwig and van Beethoven, and John and Smith, with titles and suffixes moved out of the name. A 1 name 2 Ada Lovelace 3 Lovelace, Ada 4 Dr. Grace Hopper 5 Ludwig van Beethoven 6 Smith Jr., John A B 1 first last 2 Ada Lovelace 3 Ada Lovelace 4 Grace Hopper 5 Ludwig van Beethoven 6 John Smith name first / last split_name()

Prerequisites

Bash
pip install pandas openpyxl

contacts.xlsx has ten names covering the common shapes: plain, reversed with a comma, extra spaces and all capitals, a title, a surname particle, a suffix in reversed order, two given names, two family names, a single name and an apostrophe.

Step 1 — Try the one-liner and see where it breaks

The quickest split is on the last space, so that everything before it counts as the first part:

Python
import re
import pandas as pd

contacts = pd.read_excel("contacts.xlsx")
quick = contacts["name"].str.strip().str.rsplit(" ", n=1, expand=True)
quick.columns = ["first", "last"]
print(pd.concat([contacts, quick], axis=1))

Plain names come out right. "Lovelace, Ada" gives a first name of "Lovelace,"; "Dr. Grace Hopper" makes the title part of the first name; "Ludwig van Beethoven" gets the surname "Beethoven" and a first name of "Ludwig van"; the run of spaces inside " grace HOPPER " survives in the first name, and the capitals stay as typed; and "Prince" has no last name at all. Each of those is a rule to add.

Step 2 — Normalise spaces and fix all-capital or all-lower names

Collapse runs of whitespace first, then fix the case word by word — but only for words written entirely in capitals or entirely in lower case, and never for surname particles. A word in mixed case, such as McDonald, carries information about how the person writes their name and is left alone:

Python
PARTICLES = {"van", "von", "der", "den", "de", "da", "di", "du", "la", "le", "del", "dos", "bin", "al"}

def fix_word(word):
    if word.lower() in PARTICLES:
        return word.lower()
    if word.isupper() or word.islower():
        return word.title()
    return word

def normalise(name):
    words = re.sub(r"\s+", " ", str(name)).strip().split(" ")
    return " ".join(fix_word(w) for w in words)

for raw in ["  grace   HOPPER ", "Sean O'Brien", "LUDWIG VAN BEETHOVEN", "Ronald McDonald", "mcdonald"]:
    print(f"{raw!r:24} -> {normalise(raw)!r}")

" grace HOPPER " becomes "Grace Hopper", the all-capitals Beethoven keeps a lower-case van, and McDonald is untouched. The last line shows the limit: a name typed entirely in lower case as mcdonald becomes Mcdonald, because str.title() cannot know about the internal capital. That is the case for changing case only when the input carries no case information at all — and for keeping the original next to the result.

Step 3 — Handle commas, titles, suffixes and particles

Now the splitting rules, applied in order, reusing the PARTICLES set from Step 2. A comma means Last, First order. Titles (Dr, Mrs) and suffixes (Jr, III) are moved into their own columns. Lower-case particles such as van, von, de and da belong to the surname, so the surname starts at the first particle:

Python
TITLES = {"mr", "mrs", "ms", "miss", "dr", "prof", "sir"}
SUFFIXES = {"jr", "sr", "ii", "iii", "iv", "phd", "md"}

def split_name(raw):
    s = normalise(raw)
    title = suffix = ""
    if "," in s:
        last_part, first_part = [p.strip() for p in s.split(",", 1)]
        words = last_part.split()
        if len(words) > 1 and words[-1].strip(".").lower() in SUFFIXES:
            suffix, last_part = words[-1], " ".join(words[:-1])
        return {"title": title, "first": first_part, "last": last_part, "suffix": suffix, "rule": "comma"}
    words = s.split()
    if words and words[0].strip(".").lower() in TITLES:
        title, words = words[0], words[1:]
    if len(words) > 1 and words[-1].strip(".").lower() in SUFFIXES:
        suffix, words = words[-1], words[:-1]
    if len(words) == 1:
        return {"title": title, "first": words[0], "last": "", "suffix": suffix, "rule": "single"}
    for i, w in enumerate(words[1:], start=1):
        if w.lower() in PARTICLES:
            return {"title": title, "first": " ".join(words[:i]), "last": " ".join(words[i:]),
                    "suffix": suffix, "rule": "particle"}
    rule = "two words" if len(words) == 2 else "check"
    return {"title": title, "first": " ".join(words[:-1]), "last": words[-1], "suffix": suffix, "rule": rule}

parts = pd.DataFrame([split_name(n) for n in contacts["name"]])
result = pd.concat([contacts, parts], axis=1)
print(result.to_string(index=False))

Every name gets a rule column that records how it was split. Most rows say two words, comma or particle — cases where the rule is confident. "Prince" says single. Two rows say check: "Mary Ann Evans" and "José García López". Both have three words and no particle, and they need opposite answers — Mary Ann is a two-part first name, while García López is a two-part Spanish surname. No rule based on the words alone can tell them apart.

How split_name chooses a rule A decision chart. If the name contains a comma, the part before it is the last name. Otherwise titles and suffixes are removed first. A single remaining word is a single name. A lower-case particle such as van or de starts the last name. Exactly two words split into first and last. Three or more words without a particle are split on the last space and flagged for review. What does the normalised name contain? a comma Last before the comma rule = comma one word Single name rule = single van, de, von … Particle starts surname rule = particle 2 words First and last rule = two words 3+ words Split on last space rule = check

Step 4 — Send the uncertain names for review

Split names are usually needed for something visible — a greeting, a badge, a letter — so a wrong guess is embarrassing rather than harmless. Export the rows the rules were not confident about, with the proposed split, for someone to confirm:

Python
review = result[result["rule"].isin(["check", "single"])]
print(review[["name", "first", "last", "rule"]].to_string(index=False))
review.to_excel("names_to_review.xlsx", index=False)

Once a reviewer has corrected them, keep the corrections in a lookup table keyed by the original name, and apply it before the rules on the next run — so each name is only ever reviewed once:

Python
OVERRIDES = {"José García López": {"first": "José", "last": "García López"}}

def split_with_overrides(raw):
    parts = split_name(raw)
    if raw in OVERRIDES:
        parts.update(OVERRIDES[raw], rule="reviewed")
    return parts

print(split_with_overrides("José García López"))

Step 5 — Keep the original and write the result

Write the split columns next to the original, never instead of it. The full name is the data; the split is an interpretation of it:

Python
out = result[["name", "title", "first", "last", "suffix", "rule"]]
out.to_excel("contacts_split.xlsx", index=False)
print(pd.read_excel("contacts_split.xlsx", keep_default_na=False)[["name", "first", "last"]].head(5).to_string(index=False))

keep_default_na=False on the read-back keeps empty strings as empty strings rather than turning them into NaN, which matters for single names whose last column is intentionally blank. Split a Column into Multiple Columns with pandas covers splitting other kinds of combined column.

Names a rule can split and names it should not Two panels. Names the rules split confidently: two words, Last comma First, particles such as van, titles and suffixes. Names that need a person: three words with no particle such as Mary Ann Evans or José García López, single names, and conventions that write the family name first. Split by rule Ada Lovelace Lovelace, Ada Ludwig van Beethoven Dr. Grace Hopper / Smith Jr. Flag for a person Mary Ann Evans José García López Prince (one name) family-name-first orders

Common pitfalls

  • Splitting on the first space. str.split(" ", n=1) gives "Mary" and "Ann Evans"; the last space is usually the better default.
  • str.title() on every name. It breaks McDonald, DeVries and many others; only fix single-case input.
  • Dropping the original column. You cannot undo a wrong split without it.
  • Assuming everyone has a first and last name. Leave the last name blank for single names rather than inventing one.
  • Unicode. normalise works on accented names as they are; do not strip accents to make matching easier unless a separate matching key needs it.

Performance notes

A per-row Python function handles hundreds of thousands of names in seconds. For very large contact lists, where a per-row function becomes the slow part of the job, run the vectorised one-liner first, then apply split_name only to rows that contain a comma, a particle, a known title or more than two words — usually a small fraction.

Conclusion

Split names with explicit, ordered rules: normalise whitespace and fix only single-case input, treat a comma as Last, First, move titles and suffixes into their own columns, start the surname at a particle such as van or de, split two-word names on the space, and flag single names and three-word names without a particle for a person to confirm. Record which rule applied, keep the original name, and store reviewers' corrections so each ambiguous name is decided once.

Frequently asked questions

What is the simplest way to split a name column in pandas? df"name".str.rsplit(" ", n=1, expand=True) splits on the last space, giving everything before it as the first part and the final word as the last name. It is right for simple two-word names and wrong for surnames with particles or two parts.

How do I handle "Last, First" order? Check for a comma first. When a name contains one, the part before it is the last name and the part after it the first name; split on the comma instead of on spaces.

Why not just use str.title() to fix capitals? Because it lowers every letter after the first in a word, turning McDonald into Mcdonald and DeVries into Devries. Only change the case of names that are entirely upper or lower case, and leave mixed-case names as the person wrote them.

Can a rule split every name correctly? No. Many naming conventions do not fit a first-and-last model at all — family names first, two family names, single names. Keep the original full name, split where the rule is confident, and flag the rest for review.

More on data cleaning

More on data cleaning →