How to Split Full Names Into First and Last Names in a CSV

Why splitting names goes wrong so often

Almost every CRM, email platform and mailing tool wants a first name and a last name in separate columns — personalisation, sorting, deduplication and salutations all depend on it. Almost every export, event list and lead file instead gives you one column called Full Name. Splitting it sounds like a five-minute spreadsheet job, and for a list of simple two-word names it is. The trouble is that real lists are not simple: they contain middle names, initials, double-barrelled surnames, suffixes such as Jr. and III, prefixes such as Dr., single names, company names pasted into the person field, and the occasional name in Last, First order hiding among First Last rows. A naive split at the first space corrupts all of those quietly — and quiet corruption is worse than an obvious error, because the file imports successfully and the mistakes only surface when a customer reads “Hi Mary,” under an email that should have said “Hi Mary Jane,” or worse, “Hi Van.” This guide gives you a staged method that handles the common cases automatically and flags the rest for a human, instead of pretending one formula fits every name.

First, look at your actual data before choosing a method

Sort a copy of the name column and read a few hundred rows. You are looking for four things: whether any rows are in Last, First format (a comma is the giveaway), whether titles or suffixes appear, how many rows have more than two words, and whether some rows are not people at all. In the worked example for this guide — a 2,340-row conference registration export — a quick audit finds 1,912 simple two-word names, 286 three-word names, 74 rows with a comma, 38 with a suffix or title, 19 single names, and 11 rows that are company names. Those counts decide the work: 82% of the file can be split by the simplest rule, and the method below is mostly about protecting the other 18% from that rule. If you are starting from a messy export generally, run it through the free CSV Cleaner first so stray spaces do not poison the word counts.

The staged split: comma rows, then simple rows, then the rest

Stage 1 — handle “Last, First” rows separately

Any name containing a comma is almost certainly inverted, or carries a suffix, or both: Garcia, Maria, Smith Jr., John. Filter those rows out first. For a plain inverted row, everything before the comma is the last name and everything after it is the first name (plus any middle name). Do not run the normal left-to-right split on these rows — it will put the surname in the first-name column for every single one, and because the import succeeds, nobody notices until the personalised emails go out.

Stage 2 — split the two-word rows

For rows with exactly two words and no comma, the split is unambiguous: first word to first_name, second word to last_name. In a spreadsheet, with the full name in cell A2, the first name is =TEXTBEFORE(A2," ") and the last name is =TEXTAFTER(A2," ") in current versions of Excel and Google Sheets; older versions use LEFT/FIND and RIGHT/LEN combinations to the same effect. Apply it only to the two-word subset, check a sample of results by eye, and record the count: 1,912 rows in the example, done with confidence.

Stage 3 — decide a rule for middle names, and write it down

For three-or-more-word names with no comma, you must choose: does the middle go with the first name or stand alone in a middle_name column? Most CRMs personalise from the first token only, so the practical rule used here is: first word to first_name, everything after the last space to last_name, and anything between into middle_name. That turns Mary Jane Watson into first Mary, middle Jane, last Watson — correct for personalisation. But watch the known exceptions: two-part first names (Mary Jane, Jean Luc) and multi-word surnames (Van Der Berg, De La Cruz, St John). No formula can tell Mary Jane Watson (middle name) from Mary Jane Watson where Mary Jane is the given name. The honest approach is to apply the rule, then flag every three-plus-word row into a review list; in the example that is 286 rows, of which a human scanning the list corrects 23 — about ten minutes of work that prevents the most visible mistakes in the whole file.

Titles, suffixes and single names

Strip titles (Dr., Prof., Rev.) into their own column or drop them before splitting, otherwise 38 rows in the example would produce first names literally called “Dr.” Suffixes (Jr., Sr., II, III, MD, Esq.) usually follow a comma, which is another reason comma rows go first. Single names — 19 in the example, a mix of mononyms and incomplete records — should go entirely into first_name with the last name left blank and flagged, never duplicated into both columns, because a duplicated value breaks deduplication later: the row will no longer match the same person imported properly elsewhere. For matching and merging generally, our guide to deduping a large email list shows why consistent name fields matter downstream.

Hyphens, apostrophes and capitalisation

Hyphenated names split on spaces only, never on hyphens: Anne-Marie Dupont-Smith is two words and splits cleanly if your rule counts spaces, and corrupts if it counts hyphens. Apostrophes (O'Brien, D'Angelo) are ordinary characters — the risk is not the split, it is a later system mangling the character, so keep the file in UTF-8 and test-import a few apostrophe rows first. Capitalisation deserves one warning: do not bulk-apply “proper case” to fix shouting exports such as JOHN MCDONALD. Proper-casing produces Mcdonald and Macdonald becomes Macdonald only by luck; names such as McDonald, van Dyke and DiCaprio carry meaning in their capitals. If the source is all-caps, lower-casing everything except a reviewed exception list is usually less wrong than a blind proper-case, and the review list from Stage 3 is exactly where those exceptions surface.

Verify before you import

1. Reconcile the counts. Two-word + comma + multi-word + single-name rows must equal the original row count — 2,340 in the example. A gap means a filter ate rows.
2. Rebuild and compare. Join first_name + " " + last_name back together and compare with the original (ignoring the rows you deliberately restructured). Every mismatch should be explainable.
3. Read the extremes. Sort by the new last-name column and read the top and bottom fifty rows; mis-splits cluster at the unusual names, which sort to visible places.
4. Test-import ten hard rows — one comma row, one suffix, one hyphen, one apostrophe, one single name — and check how the destination system displays them before importing the full file.

When to stop doing this by hand

The staged method above handles a few thousand rows in under an hour, most of it review time. It stops scaling when the same export arrives weekly (write the rules down once and reuse them — the counts per stage become your alarm when a run deviates), or when the file mixes several source formats at once, which is the point where a repeatable tool pass beats spreadsheet formulas you have to rebuild each time. The Data Health Score will flag blank and inconsistent name fields before and after, so you can show the improvement in one number. However you do it, the principle is the same as all cleaning work in our complete CSV cleaning guide: automate the provable cases, flag the judgement calls for a human, and never let a formula silently guess at a person’s name.