Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

TEXTSPLIT

Text & cleanup

Break one messy cell into clean columns — split an address or a full name in a single formula.

Difficulty

Good

Excel file

1What is it?

TEXTSPLIT breaks a text string into separate pieces at a delimiter and spills them across columns (and, with a second delimiter, down rows). It's Text-to-Columns as a live formula: change the source and the split updates itself, no re-running a wizard. Point it at the comma in an address, the space in a name, or a semicolon in a pasted list. Microsoft 365 / Excel 2021+.

2What it looks like

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

text: The string to break apart.
col_delimiter: What separates the pieces across columns — ", " for an address, " " for a name.
[row_delimiter]: An optional second marker that starts a new row (e.g. a line break) — for a two-way split.
[ignore_empty]: TRUE collapses back-to-back delimiters so you don't get blank cells.
[pad_with]: What to fill ragged rows with when a two-way split isn't a perfect grid.

3When you use it

Split "123 Main St, Austin, TX" into street, city, and state cells.
Break a full name into first and last across two columns.
Turn a comma- or semicolon-separated paste into a real table.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

One messy cell becomes clean pieces — TEXTSPLIT spills them down here. Edit the address below.

=TEXTSPLIT(B2, ", ")123 Main St

	A	B	C
1	Field	Value	Result
2	Address	123 Main St, Austin, TX	123 Main St
3			Austin
4			TX

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

Address (B2)

5Common errors

#SPILL!Cells where the pieces need to land aren't empty.

Fix: Clear the cells to the right (and below) of the formula.

Blank cells appearTwo delimiters sit next to each other (e.g. a double space).

Fix: Set ignore_empty to TRUE, or TRIM the text first.

#NAME?You're on a version without TEXTSPLIT.

Fix: Needs Microsoft 365. Otherwise use Data → Text to Columns, or TEXTBEFORE/TEXTAFTER.

6Better functions & alternatives

TEXTBEFORE / TEXTAFTER — Pull one specific piece instead of splitting everything.
Text to Columns — The one-off menu version (Data → Text to Columns) — static, not a live formula.
TRIM — Clean the pieces of stray spaces after splitting.

Want TEXTSPLIT already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →