Parsing a String with Dynamic Arrays

Posted on

QUESTION :

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:

qwertyuiop$

in A1, I pick a cell and enter:

=MID($A$1,ROW(1:11),1)

and the formula spills down:

enter image description here

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.

I tried:

=MID($A$1,ROW(1:LEN(A1)),1)

but this fails. My current work-around is:

=FILTER(MID($A$1,ROW(1:9999),1),MID($A$1,ROW(1:9999),1)<>"")

which seems like a really dumb way to make 11 a variable.

I offered a VBA solution, but that was rejected.

ANSWER :

Use SEQUENCE

=MID(A1,SEQUENCE(LEN(A1)),1)

enter image description here

What is nice about SEQUENCE is that if you want the array to spill horizontally then you just use:

=MID(A1,SEQUENCE(,LEN(A1)),1)

enter image description here


If you are set on Row then use:

=MID(A1,ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))),1)

One method is through use of an INDIRECT to evaluate the LEN() and concatenate it with your starting range. This is pretty close to your original formula and is an alternative.

=MID($A$1,ROW(INDIRECT("1:" & LEN(A1))),1)

enter image description here

Leave a Reply

Your email address will not be published.