Practical use of row_delimiter with TEXTSPLIT

The col_delimiter is used to spill text across columns, whereas the [row_delimiter] is used to spill text down rows. While the col_delimiter is technically a required argument, it can be omitted if the [row_delimiter] is provided. For example, =TEXTSPLIT("a,b,c",,",") will return {"a";"b";"c"} as a vertical vector.

Use both delimiters to spill a 2D array of text. For example, =TEXTSPLIT("a,b;c,d",",",";") will return a 2 x 2 array consisting of {"a","b";"c","d"}.

Use the optional [pad_with] argument to hide #N/A values, which result from a mismatched number of row and/or column delimiters in the text string (returning a jagged array). For example, =TEXTSPLIT("a,b;c",",",";",,,"") will return a 2 x 2 array consisting of {"a","b";"c",""}, which otherwise would have returned {"a","b";"c",#N/A} if the [pad_with] argument was not set.

Knowing this, many users find the simple syntax of TEXTJOIN and TEXTSPLIT convenient and easy to understand and will use them together with either BYROW-FILTER or GROUPBY to handle certain data transformation scenarios and overcome the "nested arrays are not supported" issue.

A typical example might look something like this:

=LET(
   grp, GROUPBY(tblData[Team], tblData[Member], ARRAYTOTEXT, 0, 0),
   HSTACK(TAKE(grp,, 1), TEXTSPLIT(TEXTJOIN(";",, DROP(grp,, 1)), ", ", ";",,, ""))
)

To be clear, there are many ways to accomplish this task, and this is not the method I would personally choose. While it's relatively short and easy to follow, it's also limited in the amount of data it can handle, due to TEXTJOIN's text limit of 32,767 characters. Having said that, it's a perfectly viable option for smaller datasets.