Splitting Cell into Multiple row using array

Question

I am trying to split the range of data like this :-

At first I was using this :-

=ARRAYFORMULA(IF(ROW($B$2:$B)>=(SUM($A$2:$A)+ROW()),"",IFERROR(SPLIT(LOOKUP(ROW($B$2:$B)-ROW(),IF(ROW($B$2:$B)>=(SUM($A$2:$A)+ROW()),"",SUMIF(ROW($A$2:$A),"<"&ROW($A$2:$A),$A$2:$A)),A2:A),"🙂"),"")))

But this didn't worked, I got this from another stack answer, I know this is not right :(, I can't reach anywhere with this

This is the sheet URL :- https://docs.google.com/spreadsheets/d/1oB1i0IAGoCVN0ynKBXDQcmEd3c_mpOdmWVFo7Gz-vyk/edit#gid=0

Thanks

Answer 1

REDUCE the array by SPLITting by CHAR(10):

data	Intended Result
1	1
1 2 3	1
	2
	3

=REDUCE("Intended Result",A2:A5,LAMBDA(a,c,{a;IFERROR(TRANSPOSE(SPLIT(c,CHAR(10))))}))

Answer 2

Formula for you

=TRANSPOSE(SPLIT(JOIN(CHAR(10),A2:A7),CHAR(10),1,0))

Function References

• JOIN
• SPLIT