PriestZoth
New Member
- Joined
- Jan 20, 2024
- Messages
- 2
- Office Version
- 365
- 2021
- Platform
- Windows
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
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
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.
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!
(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
Excel Formula:
=TEXTSPLIT(A2, , UNICHAR(10))
Excel Formula:
=TEXTSPLIT(A3, , UNICHAR(10))
Excel Formula:
=TEXTSPLIT(A4, , UNICHAR(10))
Excel Formula:
=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
Excel Formula:
=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.
Excel Formula:
=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!