How do you create a variable length array inside an Excel formula?

Question

I am writing a SPLIT function using the new lambda function to recursively pull items out of a text string, based on a delimiter.

The problem is that I need to keep a variable length list in memory of these items as I find them, ie a list that grows in length as I add items.

Not sure if it helps, but check out [this](https://stackoverflow.com/a/65363987/9758194) older post which I'm sure can now also be tidied up since the introduction of `REDUCE()`. — JvdV, Oct 31 '21 at 07:40

score 2 · Answer 1 · answered Oct 31 '21 at 05:56

I couldn't find a solution online, but eventually stumbled on this simple approach. This creates a new list by adding a new item B to the end of an existing list A

IF(SEQUENCE(1,COUNTA(A)+1)<COUNTA(A)+1, A, B)

I thought this might be useful for anyone doing recursive lambdas.

How do you create a variable length array inside an Excel formula?

1 Answers1