How To Concatenate A Range With A Delimiter

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.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

2 Comments

  1. Mohammed

    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……..

    • John

      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.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃