Splits text out across rows and/or columns
What does Excel's TEXTSPLIT function do?
The TEXTSPLIT function is one of Excel's text functions.
The TEXTSPLIT function works in the same way as the text-to-columns functionality in native Excel or split-by functionality in PowerQuery. It allows you to split values from a cell across rows and/or columns based on a delimiter.
The function makes use of Excel' dynamic array functionality to "spill" the results aross rows and columns.
TEXTSPLIT is a great improvement on using other text functions such as LEFT, RIGHT, MIDDLE etc to split text out from a cell.
Here's a simple example
Let's take a look at a simple example of the TEXTSPLIT function.
We would like to split the three words in cell A1 out across three separate cells, firstly across columns and secondly down rows.
=TEXTSPLIT(A1," ")
=TEXTSPLIT(A1,," ")
What does that mean in plain English?
Split the three words in cell A1 out across three separate cells, firstly across columns and secondly down rows.
Now lets look at a slightly more complicated example where we want to split by columns and rows
We would like to split the three words in cell A1 out over both rows and columns. We will split across columns using a space delimiter " " and down rows using the hyphon "-" delimiter.
=TEXTSPLIT(A1," ","-")
How do I write a formula using the TEXTSPLIT function?
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
text– The text to be split
col_delimiter – What to split columns by
[row_delimiter] – What to split rows by (optional)
[ignore_empty] – Ignore empty values. TRUE = ignore, FALSE = preserve (default).
[match_mode] – Case sensitive delimiter. TRUE = yes (default) , FALSE = no
[pad_with] – The value to pad with
What to consider when using the TEXTSPLIT function in your model
- The TEXTSPLIT function was introduced in 2019, so be aware of compatibility issues for users of previous Excel versions.
Read more about the TEXTSPLIT function on the Microsoft support page here.
