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.

 

[Fluff]

As you are dealing with a single column, I would use MAP rather then BYROW.
As to why BYROW doesn't work, I've no idea.

 

[hagia_sofia]

Workaround here would be this (provided it results in 2 columns to allow you work with the last one):

=DROP(REDUCE("",A2:A3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,";")))),1)

 

[felixstraube]

Thank you both! Both suggestion work great.

 

[hagia_sofia]

This should take the last element even if the number of elements is not the same:

=MAP(A2:A3,LAMBDA(c,
LET(
a,TEXTSPLIT(c,";"),
b,COLUMNS(a),
CHOOSECOLS(a,b))))

 

[hagia_sofia]

And BYROW option:

=BYROW(A2:A3,LAMBDA(a,CHOOSECOLS(TEXTSPLIT(a,";"),2)))

 

[felixstraube]

try this

=BYROW(A2:A3&"",LAMBDA(a,CHOOSECOLS(TEXTSPLIT(a,";"),2)))

or this:

=BYROW({"1;2";"3;4"},LAMBDA(a,CHOOSECOLS(TEXTSPLIT(a,";"),2)))

That was the reason I posted the question. I don't get it why it doesn't work. But luckily we have other options.

This should take the last element even if the number of elements is not the same:

For the last element you can also use CHOOSECOLS(array, -1).

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RoryA]

Can you tell me why? Is there a workaround?

x is an array, not a single value. You can add in INDEX:

=LET(
d, A2:A3&"",
BYROW(d, LAMBDA(x, COLUMNS(TEXTSPLIT(INDEX(x,1), ";"))))
)

 

[felixstraube]

Thanks RoryA. That's great.
Now why does BYROW transform each elemento of A3:A4&"" into an array but not each element of A3:A4?
Why does MAP not do that? Is there an explanation for that or should we simply accept that excel work like this?