Practical use of row_delimiter with TEXTSPLIT

Hi,

See the screenshot above. In cell E2 I have apply this formula:

=IFNA(TEXTSPLIT(TEXTJOIN(";",, B2:B6), ", ", ";"), "")

This part of formula TEXTJOIN(";",, B2:B6) combines all the texts into a single cell, placing a semicolon for each text of each row (in the meantime, see that in cell B3 a comma is placed after the first name of this cell).

Now we have a single text that contains two different separators (comma and semicolon).

Through the TEXTSPLIT function we can split the text into different columns and rows based on the separators. The separator ", " serves to separate the texts into columns and the separator ";" serves to separate the texts into rows.

Finally, the texts will be placed in different columns and rows.

The use of the IFNA function is done to remove #N/A from the final results.

To better understand, apply the formula step by step.

I hope I have explained the use of separators for the TEXTSPLIT function.

Regards,

IlirU

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.

Here is a use of row_delimiter.