Combining TEXTSPLIT with BYROW to return a spilled array

Question

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?

Answer 1

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," "))

Answer 2

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 this answer by @JvdV to one of my question. This is more efficient to handle multi delimiter.