**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.

**ANSWER :**

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)
```