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.

hi ,

incase i have 15000 cell in one colome and need to convert it as above,what is the formola?!!

=A2&”,”&A3&”,”&A4&”,”&A5&”,”&A6&”,”&A7&”,”&A8&”,”&A9&”,”&A10&”,”&A11……..

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.