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.

=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
    If Len(vntBuild) > 0 Then
        MConcate = Left(vntBuild, Len(vntBuild) - Len(delimiter))
    End If

End Function