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