TEXTSPLIT not working as expected inside of BYROW. Workaround?

[felixstraube]

Hi all.
I was working on a LET formula that returned an array. Each element of the array contained text separated by semicolons. Then I wanted to work on each element with BYROW and separate the text of each element into an with TEXTSPLIT and work with the value of the last column. But, it wasn't working as expected. TEXTSPLIT apparently only returned the first element.

A simplified example would be the following:

As you can see, when I concatenate the input array with "" (which I suppose is something similar as the data I got in the formula I was working on), the formulas stops working as expected.
As if when the input is a range it works, but if it is an array (even a hard coded array, {"1;2";"3;4"}), it won't work.
Can you tell me why? Is there a workaround?

Thanks in advance!

Book9.xlsx
	A	B	C	D
1	Input		Working	Not working
2	1;2		2	1
3	3;4		2	1
4
5			2	1
6			4	3
7
8			1|2	1
9			3|4	3
Sheet9

Cell Formulas
Range	Formula
C2:C3	C2	=LET( d, A2:A3, BYROW(d, LAMBDA(x, COLUMNS(TEXTSPLIT(x, ";")))) )
D2:D3	D2	=LET( d, A2:A3&"", BYROW(d, LAMBDA(x, COLUMNS(TEXTSPLIT(x, ";")))) )
C5:C6	C5	=LET( d, A2:A3, BYROW(d, LAMBDA(x, CHOOSECOLS(TEXTSPLIT(x, ";"),-1))) )
D5:D6	D5	=LET( d, A2:A3&"", BYROW(d, LAMBDA(x, CHOOSECOLS(TEXTSPLIT(x, ";"),-1))) )
C8:C9	C8	=LET( d, A2:A3, BYROW(d, LAMBDA(x, TEXTJOIN("|",,TEXTSPLIT(x, ";")))) )
D8:D9	D8	=LET( d, A2:A3&"", BYROW(d, LAMBDA(x, TEXTJOIN("|",,TEXTSPLIT(x, ";")))) )
Dynamic array formulas.

 

[RoryA]

MAP is designed to process each element of an array one at a time. BYROW is designed to iterate through each row. If there's only one column, then one row = one cell, and TEXTSPLIT will work with that. It doesn't however like arrays. If you use something like:

=TEXTSPLIT({"A;B"},";")

it will only return A (hence your 1 column result)

 

[felixstraube]

Thank you so much for your detailed and clear explanation. I really appreciate the time and effort you put into helping me understand this.