CODEWITHSUNDEEP
arraysexcelexcel-2007excel-2010
Suraj
Suraj

Reputation: 36597

Append or prepend to array without VBA/UDF

In Excel, is there a built-in formula/function to append or prepend a value to an array? So suppose I have my own UDF, MyFunc(...) that returns an array of values. Can I append or preprend a single value to this result using a reference to a single cell?

Upvotes: 3

Views: 4237

Answers (3)

Arpp
Arpp

Reputation: 301

Yes, you can prepend or append a value with Excel for Microsoft 365 (which is not exactly what you were looking for, but anyway...) by using VSTACK. It appends arrays vertically and in sequence to return a larger array (Microsoft) :

=VSTACK(array1,[array2],...)

If you just have one value to prepend to your array, you can do it like this :

=VSTACK("my value", yourArray)

In your specific case (where A2 is the value you want to prepend and MyFunc your function returning an array) :

=VSTACK(A2, MyFunc())

You can also append the value by changing parameters' order :

=VSTACK(MyFunc(), A2)

And last but not least, you can also prepend or append several values :

=VSTACK(A2, A3, MyFunc(), A4)
=VSTACK(MAP(SEQUENCE(nbOfValues), LAMBDA(x, "new value")), MyFunc())

Upvotes: 0

JMax
JMax

Reputation: 26601

As we discussed in the comments, here is a possible answer:

AFAIK, no formula can do this and there are no append function in VBA. Yet, you could use the ConcatenateArrays function from Chip Pearson

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53136

This can be achieved using standard Excel array functions.

For illustration, lets assume MyFunc is

Function MyFunc(r As Range) As Variant
    MyFunc = r.Value
End Function

Where the passed range is more than one cell, this returns a two-dimensional array.

When entered as an array formula like =D1&MyFunc(A2:C7)&E1 this prepends D1 and appends E1

It can also be used in a standard non-array formula, where an array parameter is expected, as shown in cell D12 below

enter image description here

Upvotes: 2

Related Questions