single-formula combining of multiple dynamic arrays (VSTACK?)

[PriestZoth]

Hello there!
(this is my first post, so I expect to fail at being clear.. apologies in advance! I also will write inelegantly and breach the formatting etiquette: I apologise for that too)

I have a problem with creating a single formula that produces a single dynamic array which combines multiple dynamic arrays.

For example, i have a range of four cells stacked vertically A2:A5.
Each cell contains text that is sometimes separated by ALT+Enter / Unichar(10) in two or more lines. In total, there are 8 lines split unevenly across 4 cells.
I want to achieve a range of cells, stacked vertically, in which each single line in the original four cells A2:A5 is stored individually in its own cell: in the example I'm using, I should get 8 cells in a vertical range.

I would like to have this done with a single formula.
Ideally, for each cell cell, the formula should create an array (let's call it a sub-array) of one or more cells (depend on how many lines each cell contains).
The final result of the formula should be all the sub-arrays combined into a single dynamic array.

I thought of getting to it first step by step, then merging each step back into a single formula.
First, easy enough, I created single independent formulas, using TEXTSPLIT on each cell referenced individually (i.e. A2, A3, A4, A5); I spaced these formulas out appropriately (to avoid the #SPILL! error) in Column C

 =TEXTSPLIT(A2, , UNICHAR(10))

 =TEXTSPLIT(A3, , UNICHAR(10))

 =TEXTSPLIT(A4, , UNICHAR(10))

 =TEXTSPLIT(A5, , UNICHAR(10))

Each of the four formulas above produces a single or multi cell array, exactly as expected.
Then I can merge each of these invididual arrays using VSTACK in the formula below

 =VSTACK(C2#, C3#, C6#, C8#)

This works: I get an array of 8 lines.
(I tried to show this in the attached "screenshot.png").

I thought I could merge the components of this two-steps solution with the formula below, in which
-- I change the cell references in teh TEXTSPLIT function from single cells to the array, and then
-- nest the TEXTSPLIT function into the VSTACK function.

=VSTACK(   TEXTSPLIT([B]A2:A5[/B], , UNICHAR(10))   )

Needless to say, no joy! This produces an array of only four cells, showing each only the first line of each of the original cells A2:A5.
Please see the screenshot here below, showing on the left the output dynamic array, and on the right the fomula it is based on.

I guess I'm approaching this from the wrong direction..
What is the right approach to this?

(I tried something with MAKEARRAY, but the LAMBDA is something that I still don't understand, so I cannot even tell whether that's a route I should go down on)
(I tried to use the mini-sheet add-in, but could not get it to work.. I'll get there, in time)

Thank you all!

 

[Fluff]

How about

=REDUCE(TEXTSPLIT(A2,,CHAR(10)),A3:A5,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,CHAR(10)))))

 

[PriestZoth]

Hi Fluff..
well, that is spot on what I wanted.
Not that I understand (yet) how it works..
All my talk of myself being good with Excel feels quite senseless, now.. but ehi, at least I've got some homework for the weekend, and two functions to study.
(I'm kinda glad that I was already somewhat aware -- at a stretch -- that the LAMBDA would be part of the solution)
Thank you so much for this

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Plod518]

In this particular instance this would also work (if you wanted to avoid the reduce + lambda complexity).

=TEXTSPLIT(TEXTJOIN(CHAR(10),TRUE,A2:A5),,CHAR(10)

- Joins all the text with char(10) and then splits of char(10). For anything more complex it probs wouldn't work.

Found this when trying to understand how to use the reduce/lambda piece for my own problem (that I haven't solved).....