How to split one CSV column into multiple columns (e.g. full name into first/last)
Splitting a combined field like 'full name' or 'city, state' into separate columns, and the edge cases that trip up a naive split.
Before you work through this by hand: you can check your own CSV free and see which of these problems your file actually has — duplicate rows, near-duplicates, broken quoting, encoding damage. It runs in your browser tab, nothing is uploaded, and there's no signup or email.
The simple case: a single, consistent separator
If every value uses the same separator in the same position (e.g. always exactly one space between first and last name), a spreadsheet's Data → Text to Columns (Excel) or Split text to columns (Google Sheets) handles it directly — choose the delimiter (space, comma) and it creates new columns.
The pandas equivalent
import pandas as pd; df = pd.read_csv('file.csv'); df[['first_name', 'last_name']] = df['full_name'].str.split(' ', n=1, expand=True) — the n=1 limits it to a single split, so "Mary Jane Smith" becomes first_name="Mary", last_name="Jane Smith" rather than producing a third, unwanted column.
Where naive splitting breaks
Real name/address data rarely follows one consistent pattern: some people have no last name in the data, some have multiple middle names, some records include a suffix ("Jr.", "III"), and "city, state" fields sometimes include a country too. A fixed-position split will misalign these rows silently — it's worth spot-checking a sample of the split results, not just trusting the first few rows.
When you need this before deduplication
Splitting a combined field can also help with near-duplicate detection — comparing "last name" alone is sometimes a better matching key than comparing the whole combined field, especially when middle names or nicknames vary between records of the same person. See our duplicate-removal guide for more on choosing good matching keys. Need to go the other direction instead? See our guide to combining columns.
See CSV Repair & Deduper (offline browser tool) — $19 one-time