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.
incase i have 15000 cell in one colome and need to convert it as above,what is the formola?!!
If you have Excel 2016, use the TEXTJOIN formula for this. With 15,000 cells, it’s likely you’ll hit the character limit per cell though.