Combining TEXTSPLIT with BYROW to return a spilled array-CodePudding

I can use TEXTSPLIT() on an individual cell with no problem, but I want to use a single formula to split a list of names.

This works:

However, this doesn't:

What am I doing wrong? BYROW() works fine with most functions, but it doesn't like TEXTSPLIT() for some reason.

What is the solution?

CodePudding user response：

The error is due to the fact that, when using BYROW, the LAMBDA must return only a single value, not two values.

There may be more efficient methods, but one way of getting your desired output to spill over the range with just a single formula:

 =HSTACK(TEXTBEFORE(F2:F4," "),TEXTAFTER(F2:F4," "))

CodePudding user response：

REDUCE() could be good choice in this case. Reduce function can output result dynamically expanded both horizontally and vertically.

=IFERROR(DROP(REDUCE(0,A2:INDEX(A2:A50000,COUNTA(A2:A50000)),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1),"")

See