There are two ways of concatenating cells in Excel, either with the built-in CONCATENATE function or using &.

But let’s say you wanted to concatenate a large number of cells into one value and have them separated by a delimiter character like a comma, this means referencing each cell individually using either CONCATENATE or & and adding “,” between each cell reference.

Pretty tedious stuff. This is a good example where creating a short user defined function in VBA could be a big help.

In this example let’s concatenate column 1 and have the list separated by commas.

Using the CONCATENATE function we could use the formula:

=CONCATENATE(A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,",",A9,",",A10,",",A11)

Using the & we could use the formula:

=A2&","&A3&","&A4&","&A5&","&A6&","&A7&","&A8&","&A9&","&A10&","&A11

Both not very practical as the range gets bigger.

A better solution is to create a user defined function in VBA. Something like this will do the trick.

Function ConcatDelim(ConcatRange As Variant, Delimiter As Variant) As String

Dim Test As Boolean
Test = True

For Each i In ConcatRange
    If Test Then
        ConcatDelim = i
        Test = False
    Else
        ConcatDelim = ConcatDelim & Delimiter & i
    End If
Next i

End Function

With this VBA code, the formula needed becomes this.

=ConcatDelim(A2:A11,",")

Much more simple.