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:
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 Else ConcatDelim = ConcatDelim & Delimiter & i End If Next i End Function
With this VBA code, the formula needed becomes this.
Much more simple.