Excel Snippets: TEXTSPLIT and TEXTJOIN Functions

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

TEXTSPLIT and TEXTJOIN are opposites of each other; one splits a string apart and the other joins values together. Both make working with delimited text far easier than the older approaches of nested formulas or the Text to Columns wizard.

TEXTSPLIT splits a text string into an array of values using a specified delimiter, spilling the results across columns or down rows. It was introduced in Microsoft 365 in 2022:

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

text is the string to split; col_delimiter is the character to split on horizontally, and can be supplied as an array such as {“,”,”;”} to split on multiple delimiters; row_delimiter (optional) is a second delimiter to split on vertically, enabling a two-dimensional split; ignore_empty (optional) suppresses empty cells caused by consecutive delimiters when set to TRUE; match_mode (optional) controls case sensitivity; and pad_with (optional) specifies a value to use for padding when the result is a non-rectangular array.

To split a comma-separated list in cell A2 across columns:

=TEXTSPLIT(A2, ",")

To split the same list down rows instead, supply the delimiter as the row_delimiter and leave the column delimiter empty:

=TEXTSPLIT(A2, , ",")

Because TEXTSPLIT returns a dynamic spill range, make sure the cells to the right or below are empty before entering the formula, otherwise a #SPILL! error will result. It is also only available in Microsoft 365 and Excel 2024.

TEXTJOIN goes the other way, combining text from a range of cells into a single string with a delimiter placed between each value. Unlike concatenating with the & operator, it can skip blank cells automatically and works on entire ranges rather than requiring each cell to be referenced individually:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

delimiter is the character or string to place between each joined value, use “” for no delimiter; ignore_empty is TRUE or FALSE, and when TRUE skips blank cells without inserting extra delimiters; and text1, text2, ... are the values or cell ranges to join, up to 252 arguments.

To join the values in A2:A10 with a comma and a space between each, skipping any blank cells:

=TEXTJOIN(", ", TRUE, A2:A10)

TEXTJOIN is available in Excel 2019, Excel 2021, and Microsoft 365, so it has a wider reach than TEXTSPLIT.