Concatenating cells with a delimiter

Earlier today, I was looking for an excel function somewhat similar to ruby’s join function for arrays. Given a range of cells and a character to use as a delimiter, I wanted the function’s output to be a concatenation of the cell values alternating with the delimiter. I googled and was easily able to find a solution that suited my needs well enough.

Usage:
=MConcate(B6:AX6,"|") – Concatenates B6 to AX6 with “|” as delimiter

VBA code:

Public Function MConcate(Data As Variant, delimiter As Variant) As Variant

    Dim vntBuild As Variant
    Dim vntItem As Variant

    For Each vntItem In Data
        vntBuild = vntBuild & vntItem & delimiter
    Next
    If Len(vntBuild) > 0 Then
        MConcate = Left(vntBuild, Len(vntBuild) - Len(delimiter))
    End If

End Function

Reference: http://www.ozgrid.com/forum/showthread.php?t=86223

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s