How To Concatenate A Range With A Delimiter

2016-04-20

VBA

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:

=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 freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

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

    Reply
    • 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.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This