Append or prepend to array without VBA/UDF

By | February 12, 2018
Questions:

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?

Answers:

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

Questions:
Answers:

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 *