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 older post which I'm sure can now also be tidied up since the introduction of REDUCE(). — JvdV
– JvdV, Commented Oct 31, 2021 at 7:40

dbb · Accepted Answer · 2021-10-31 05:56:06Z

2

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.

answered Oct 31, 2021 at 5:56

dbb

2,87719 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related