Append or prepend to array without VBA/UDF

By | February 12, 2018

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?


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


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

Leave a Reply

Your email address will not be published. Required fields are marked *