# 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: 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.

Use SEQUENCE

``````=MID(A1,SEQUENCE(LEN(A1)),1)
`````` 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)
`````` 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)
`````` 