Using the dynamic spill-down arrays recently introduced in Excel 365, I wrote a super simple formula to parse the characters in a string into single cells. For example, if I have:
in A1, I pick a cell and enter:
and the formula spills down:
This avoids entering
=MID($A$1,ROWS($1:1),1) and manually copy it downwards.
The problem I have is with the number 11. I use 11 because I know the length of A1 is 11. I would like to change the
MID() formula to make it adjust to the length of A1 rather than me re-typing the formula.
but this fails. My current work-around is:
which seems like a really dumb way to make 11 a variable.
I offered a VBA solution, but that was rejected.
What is nice about SEQUENCE is that if you want the array to spill horizontally then you just use:
If you are set on Row then use: