TEXTSPLIT combined with BYROW returns an unexpected result when using an array of strings as input

Question

I have the following simple test:

=LET(input, {"a,b;c,d;" ; "e,f;g,h;"}, 

  BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

it should return input (since TEXTJOIN is the inverse operation of TEXTSPLIT), but it returns instead:

If I run the same but using a range, it works as expected:

Per TEXTSPLIT documentation there is no constraint on using an array of strings combined with BYROW.

Is it a bug? if so, where it can be reported?

Any help is appreciated. Thanks

Answer 1

Perhaps the second behaviour is related to implicit intersection

> If the value is an array, then pick the top-left value.

Answer 2

There are 2 things going here.

The first is that BYROW behaves differently when passed a vertical array and a vertical reference. When passed a vertical array, the lambda receives an array with only one row in it, i.e, {"a,b;c,d;"} but when passed a vertical reference the lambda receives a scalar, i.e., "a,b;c,d;". You can see this by calling TYPE in your lambda. I suspect BYROW is basically calling INDEX(input, row_number, 0) which exhibits similar behaviour, for each row.

The second is that TEXTSPLIT does something weird when passed an array. It's effectively trying to create a 2D array of 2D arrays, which doesn't fit in a 2D spreadsheet. So, instead it just picks first element of the inner 2D array. I don't know why it doesn't return #VALUE! instead. Example:

TEXTSPLIT({"1,2;3,4;","5,6:7,8";"9,10;11,12","13,14;15,16"},",",";", TRUE)

This behaviour seems to apply to any function that returns an array when you pass it a scalar. Try, for example,

SEQUENCE({2,3})

Whether one or both of these are bugs, I'm not sure. I lost most of a day this week debugging a similar issue. It would be nice if someone from MS could clarify and, perhaps, expand the documentation.

Answer 3

It seems to be a bug in BYROW function working with TEXTSPLIT. Changing BYROW with MAP that in this case serves for the same purpose it works:

=LET(input, {"a,b;c,d;";"e,f;g,h;"},

MAP(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

Answer 4

@Rand2201, I don't follow your answer, I don't have an issue using a range, the issue is using an array of strings. This is a sample testing TEXTSPLIT combined with BYROW from my real sample. What is Fixit? Thanks