Guide Text and regex
Splitting a column without doing it again next month
Text to Columns works, and it is a one-off action on static data. TEXTSPLIT does the same thing as a formula, so next month's rows split themselves.
=TEXTSPLIT(A2,", ") splits a cell across columns at each
separator, and the result updates when the cell changes. TEXTBEFORE and
TEXTAFTER take just one side, which is usually what you want for a first and last name.
All three need Microsoft 365; on older versions use Text to Columns, Power Query, or
LEFT with FIND.
Text to Columns is an action, not a formula
Data → Text to Columns is excellent and it has one property people forget: it happens once, to the cells that existed at the time.
Rows added afterwards are not split. Nothing warns you — the new rows just sit there unsplit at the bottom, and whatever you built on the split columns quietly stops covering them.
It also overwrites whatever is to the right, without much of a warning, which has destroyed more than one column of notes.
A formula has neither problem: it produces a copy, it updates, and it cannot overwrite anything because Excel refuses to spill onto occupied cells.
| What you see | What actually happened | What fixes it |
|---|---|---|
| New rows are not split | Text to Columns ran once, before they existed | Use a formula instead |
| It overwrote the column beside it | Text to Columns writes rightwards | Undo, insert empty columns, redo |
#SPILL! from TEXTSPLIT | Something is in the way of the result | Clear the cells to the right |
| Names with a middle name break | More separators than expected | Use TEXTBEFORE and TEXTAFTER |
#NAME? | Not available in your version | Power Query, or LEFT with FIND |
How to split text with a formula
Split across columns with TEXTSPLIT
The separator is text, so a comma and a space is ", ":
=TEXTSPLIT(A2,", ")The result spills rightwards. Pass a second separator to split down rows as well
— =TEXTSPLIT(A2,",",";") makes a grid.
Take one side with TEXTBEFORE or TEXTAFTER
Better than splitting when the number of parts varies:
=TEXTBEFORE(A2," ")
=TEXTAFTER(A2," ",-1)The -1 means “the last one”. So a middle name is simply
ignored, where TEXTSPLIT would have put it in a column of its own and pushed the surname
sideways.
Handle the rows that do not match
A single-word name has no space in it, and by default that is an error:
=TEXTBEFORE(A2," ",1,,,A2)The last argument is what to return when the separator is not found — here, the
whole cell. IFERROR does the same job and reads more clearly.
Without these functions: LEFT and FIND
The universal version:
=LEFT(A2,FIND(" ",A2&" ")-1)The &" " is what stops it erroring on a cell with no space in it
— it guarantees there is always one to find. This works in every version of Excel, in Sheets
and in LibreOffice.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Getting the last word out
Harder than the first, because you cannot search backwards. The standard trick is to pad every separator until it is enormous, then take the tail:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))Replace each space with 99 spaces, take the last 99 characters, trim. The last word is the only one that can survive that. Ugly, and it works everywhere — which is why it has been copied into so many workbooks.
When Power Query is the better answer
If the split is part of a monthly import, do it in Power Query rather than with formulas. Its Split Column handles by delimiter, by position, and by the change from letters to digits — and it runs on refresh.
Names deserve care
Splitting a full name is never fully solvable. van der Berg, O'Brien,
Maria de los Angeles and single-name people all break a simple rule. Take everything
after the last space as the surname, then look at the result. On a list of a few hundred it
takes a minute, and getting somebody's name wrong in a letter is worth more than a minute.
Google Sheets
Sheets has SPLIT, which is older and simpler than TEXTSPLIT. It has no
direct TEXTBEFORE or TEXTAFTER, but INDEX(SPLIT(A2," "),1)
gets the first piece.
Questions people ask
What is the difference between TEXTSPLIT and Text to Columns?
Text to Columns is a one-off action on the cells that exist at the time. TEXTSPLIT is a formula, so it updates and covers rows added later.
How do I get the last word of a cell?
TEXTAFTER(A2," ",-1) on Excel 365, where -1 means the last occurrence. On older versions, substitute each space with 99 spaces and take the last 99 characters.
What happens when the separator is not there?
TEXTBEFORE and TEXTAFTER return an error by default. Give them a fourth argument for what to return instead, or wrap them in IFERROR.
Why do I get #SPILL! from TEXTSPLIT?
The result needs several columns and something is in the way. Clear the cells to the right of the formula.
What should I use on Excel 2019?
Text to Columns for a one-off, Power Query for anything repeated, or LEFT with FIND for a formula that works in every version.
Is splitting names reliable?
No rule handles every name. Take everything after the last space as the surname, then check the result by eye — double-barrelled surnames and name particles will break any simple rule.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.