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