TEXTSPLIT has issue when sent a param for text a array

Question

When sent a array to text param to textsplit function her always return a one column.

Trying to force a row break with ; passing col and row delimiter

Or just use only column delimiter

Anyone confirm this a bug, have a workaround for this?

Topic set to other= same case on Mac and Windows

I suppose the right result would be:

Answer 1

Use this formula:

=TEXTSPLIT(TEXTJOIN(";",TRUE,Table1[Column]),"/",";")

Kevin

Answer 2

It's not a bug. The function is designed to operate on a single text string and split it out into rows and columns. When you pass an array to it, the TEXTSPLIT function does not actually see the array. The Excel formula engine interprets the array before passing it to TEXTSPLIT and it doesn't know what TEXTSPLIT will do. So it assumes that, since you passed a single column of values in, you will get a single column of values out and the rest is discarded.

Kevin

Answer 3

Hi,

Enter this formula in cell N3 and drag down

=TEXTSPLIT(SUBSTITUTE(L3,";",""),"/")

Hope this helps.

Answer 4

This formula eliminates the #N/A errors:

=IFERROR(TEXTSPLIT(TEXTJOIN("~",TRUE,Table1[Column]),"/","~"),"")

Kevin