TEXTSPLIT and TEXTJOIN in Excel

TEXTSPLIT breaks one cell of delimited text into multiple cells, and TEXTJOIN does the reverse — combining a range into one string with a delimiter. Together they replace Text to Columns and long & concatenation chains with single formulas.

How Do You Split Delimited Text With TEXTSPLIT?

Give TEXTSPLIT the text and a column delimiter, and it spills each piece into its own cell: =TEXTSPLIT(text, col_delimiter, [row_delimiter]).

=TEXTSPLIT("red,green,blue", ",")
' Spills across: red | green | blue

=TEXTSPLIT("a=1;b=2;c=3", "=", ";")
' Second delimiter splits into rows too:
' a | 1
' b | 2
' c | 3

=TEXTSPLIT("one, two,  three", ", ", , TRUE)
' 4th argument ignore_empty=TRUE drops blank pieces

Unlike Text to Columns, the result recalculates when the source cell changes, and it never overwrites data — you get a #SPILL! error instead.

How Do You Join a Range With a Delimiter Using TEXTJOIN?

TEXTJOIN takes a delimiter, an ignore_empty flag, and one or more ranges: =TEXTJOIN(delimiter, ignore_empty, range1, ...).

=TEXTJOIN(", ", TRUE, A2:A5)
' A2:A5 = red, green, (blank), blue
' Result: "red, green, blue"

=TEXTJOIN(" - ", TRUE, B2, C2, D2)
' Joins individual cells: "West - Q3 - 500"

What Does ignore_empty Do in TEXTJOIN?

With ignore_empty set to TRUE, blank cells are skipped instead of producing doubled delimiters like "red,,blue".

=TEXTJOIN(",", FALSE, A2:A5)   ' "red,green,,blue"
=TEXTJOIN(",", TRUE,  A2:A5)   ' "red,green,blue"

Set it to FALSE only when position matters, such as rebuilding a fixed-width CSV where an empty field still needs its slot.

When Should You Use TEXTBEFORE and TEXTAFTER Instead?

When you only need one piece — not every piece — TEXTBEFORE and TEXTAFTER grab the text on either side of a delimiter without spilling.

=TEXTBEFORE("[email protected]", "@")   ' "smith"
=TEXTAFTER("[email protected]", "@")    ' "acme.com"
=TEXTAFTER("a/b/c/d", "/", -1)       ' "d" (last occurrence)

These ship alongside TEXTSPLIT in Excel 365 and cover most of what older LEFT/MID/FIND text formulas were used for.

Common Pitfalls

• Version support: TEXTSPLIT, TEXTBEFORE, and TEXTAFTER require Excel 365 (2022+). TEXTJOIN works from Excel 2019 onward.
• Numbers become text: TEXTSPLIT output pieces are text. Wrap in VALUE() or add 0 before doing math on them.
• Delimiter not found: TEXTBEFORE/TEXTAFTER return #N/A if the delimiter is missing — supply the if_not_found argument.
• CONCATENATE has no delimiter argument: if you're typing the separator between every cell reference, you want TEXTJOIN instead.