Thursday, June 30, 2016

Join Function for mutlple cells

I've found that joining a range of cells together into a single delimited field is really useful.  Excel in the Data tab has the ability to take delimited text in a single column and move it into multiple columns, but this little function goes the other way.

Here is the code:
===========================================================
Public Function Jointext(r As Range, del As String) As String

Dim b As String
For a = 1 To r.Count
    If IsEmpty(r.Value2(1, a)) = False Then
    b = b & r.Value2(1, a) & del
    End If
Next
Jointext = Left(b, Len(b) - Len(del))
End Function
==============================================================

It's very simple,  I'm iterating a range array and putting the values into a single string delimited by the del string.  Then after that, I'm removing the last delimiter.

Simple

No comments:

Post a Comment