Make a formula spill

[Stephen_IV]

Good morning, the formula below is doing exactly what I need, except, How do I make it spill?? Any help would be appreciated.

Dynamic Array Split Schools.xlsm
	A	B	C	D
1	TWINS , ASTROS , BOSTON	TWINS	ASTROS	BOSTON
2	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
3	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
4	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
5	ROYALS , RANGERS	ROYALS	ROYALS	RANGERS
6	TWINS , ASTROS , BOSTON	TWINS	ASTROS	BOSTON
7	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
8	ANGELS , YANKEES	ANGELS	ANGELS	YANKEES
Sheet2

Cell Formulas
Range	Formula
B1:D8	B1	=LET( SplitArray, TEXTSPLIT(A1, ","), WordCount, COUNTA(SplitArray), IF( WordCount = 2, CHOOSE({1,2,3}, INDEX(SplitArray, 1), INDEX(SplitArray, 1), INDEX(SplitArray, 2)), IF( WordCount = 3, CHOOSE({1,2,3}, INDEX(SplitArray, 1), INDEX(SplitArray, 2), INDEX(SplitArray, 3)), {"","",""} ) ) )
Dynamic array formulas.

 

[6StringJazzer]

I don't understand your question. You have a formula in column B and it is spilling into columns C and D.

What is the result you want to see?

 

[Stephen_IV]

Thanks for responding. I would like the whole range to spill not just the row.

 

[Fluff]

How about

=DROP(REDUCE("",A1:A8,LAMBDA(x,y,LET(SplitArray, TRIM(TEXTSPLIT(y, ",")),WordCount, COUNTA(SplitArray),VSTACK(x,IF(WordCount = 2,CHOOSECOLS(SplitArray,1,1,2),IF(WordCount = 3,SplitArray,{"","",""})))))),1)

 

[Stephen_IV]

Thanks again Fluff! That is exactly what I am looking for!! Have a great day!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[6StringJazzer]

I see. Now that I have tried Fluff's formula, I see that you want to use just one formula instead of one formula per row.

 

[Peter_SSs]

Another variation

23 08 20.xlsm
	A	B	C	D
1	TWINS , ASTROS , BOSTON	TWINS	ASTROS	BOSTON
2	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
3	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
4	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
5	ROYALS , RANGERS	ROYALS	ROYALS	RANGERS
6	TWINS , ASTROS , BOSTON	TWINS	ASTROS	BOSTON
7	DODGERS , BOSTON	DODGERS	DODGERS	BOSTON
8	ANGELS , YANKEES	ANGELS	ANGELS	YANKEES
Split & spill

Cell Formulas
Range	Formula
B1:D8	B1	=TEXTSPLIT(REPLACE(REDUCE("",A1:A8,LAMBDA(a,b,a&"|"&IF(COLUMNS(TEXTSPLIT(b,","))=2,SUBSTITUTE(b,",",", "&LEFT(b,FIND(",",b)),1),b))),1,1,"")," , ","|")
Dynamic array formulas.

 

[Stephen_IV]

Cool, thanks, Peter. I appreciate it.

 

[Peter_SSs]

Cheers