I am testing the following simple case:

=LET(input, {"a,b;c,d;" ; "e,d;f,g;"},
  BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

since the TEXTJOIN is the inverse operation of TEXTSPLIT, the output should be the same as input without the last ;, but it doesn't work like that.

If I try using a range instead it works:

It works for a single string:

=LET(input, "a,b;c,d;", TEXTJOIN(";",,TEXTSPLIT(input,",",";", TRUE)))

it returns: a,b;c,d

What I am doing wrong here? I think it might be a bug. Per TEXTSPLIT documentation there is no constraint of using TEXTSPLIT combined with BYROW when using an array of strings.

CodePudding user response：

Not sure if this would classify as an answer but thought I'd share my attempt at it.

I don't think the problem here is TEXTSPLIT(). I tried different things. 1st I tried to incorporate FILTERXML() to do the split, with the exact same result. For good measure:

=BYROW({"a,b;c,d;","e,d;f,g;"},LAMBDA(item,TEXTJOIN(";",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(item,",",";"),";","</s><s>")&"</s></t>","//s"))))

Then I tried to enforce array usage with T(IF(1,TEXTSPLIT("a,b;c,d;",{",",";"},,1))) but Excel would not budge.

The above lead me to believe the problem is in fact BYROW() itself. Even though documentation says the 1st parameter takes an array, the working with other array-functions do seem to be buggy and you could report it as such.

For what it's worth for now; you could use REDUCE() as mentioned in the comments and in the linked answer however I'd preserve that for more intricate stacking of uneven distributed columns/rows. In your case MAP() will work and is simpler than BYROW():

=LET(input, {"a,b;c,d;";"e,d;f,g;"},
  MAP(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

And to be honest, this is kind of what MAP() is designed for anyway.