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.

Concatenate-A-Range-With-Delimiter How To Concatenate A Range With A Delimiter

Using the CONCATENATE function we could use the formula:


Using the & we could use the formula:


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
        ConcatDelim = ConcatDelim & Delimiter & i
    End If
Next i

End Function

With this VBA code, the formula needed becomes this.


Much more simple.