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