String concatenation is like the weather…
…everyone complains about it, but nobody does anything about it. Well, certainly not Microsoft, anyhows. But Nigel Heffernan at Excellerando.Blogspot.com has. A whiles back in 2012 he put up some nifty code for joining and splitting two dimensional arrays that I just stumbled across. I thought I’d have a go at turning the join one into a function that can be called from the worksheet, and add a few more options while I’m at it. More butchering than tweaking, as you’re about to see.
My revision can be called from the worksheet, and has the following arguments:
=JoinText(Array,[Delimiter],[FieldDelimiter],[DelimitEnd],[SkipBlanks],[Transpose])
Yes, more arguments than at my last social outing. Most are optional and have defaults. Take that how you will. The default delimiter is a comma. The Field Delimiter is a separate Delimiter that gets added if your input array is 2D. Default is also a comma. DelimitEnd puts an extra Delimiter on the end if you want one. Aesthetics only, really. The rest are explained below.
First, the result:
- That block in the middle is my data.
- Column D shows the result if you point the function at each respective row
- Row 9 shows the result of pointing the function at each respective column
- In rows 13 to 16 you see the result of pointing it at the entire 2D block of data, under different settings.
Those last two results are what happens if the data is laid out by row and then by column, and you’ve incorrectly told the UDF to transpose the input array. Because by default, the UDF expects the data will be laid out by column and then by row , like this:
Hard to explain. Even harder to program.
You might notice it skips blanks. It doesn’t have to, if you don’t want it to:
And it doesn’t need your two arrays to be the same size:
Here’s the code and workbook:
Join Function_20141114 v6
Optional Delimiter As String = ",", _
Optional FieldDelimiter As String = ",", _
Optional DelimitEnd As Variant = False, _
Optional SkipBlanks As Boolean = False, _
Optional Transpose As Boolean = False) As String
‘Based on code from Nigel Heffernan at Excellerando.Blogspot.com
‘http://excellerando.blogspot.co.nz/2012/08/join-and-split-functions-for-2.html
‘ Join up a 2-dimensional array into a string.
‘ Test with something like ? JoinText2([A2:C8]," ",".",0,1,"!")
‘ ####################
‘ # Revision history #
‘ ####################
‘ Date (YYYYMMDD) Revised by: Changes:
‘ 20141114 Jeff Weir Turned into worksheet function, added FinalDelimiter and Transpose options
Dim InputArray As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngNext As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long
Dim arrTemp1() As String
Dim arrTemp2() As String
If target.Rows.Count = 1 Then
If target.Columns.Count = 1 Then
GoTo errhandler ‘Target is a single cell
Else
‘ Selection is a Row Vector
InputArray = Application.Transpose(target)
Transpose = True
End If
Else
If target.Columns.Count = 1 Then
‘ Selection is a Column Vecton
InputArray = target
Else:
‘Selection is 2D range. Transpose it if that’s what the user has asked for
If Transpose Then
InputArray = Application.Transpose(target)
Transpose = True
Else: InputArray = target
End If
End If
End If
i_lBound = LBound(InputArray, 1)
i_uBound = UBound(InputArray, 1)
j_lBound = LBound(InputArray, 2)
j_uBound = UBound(InputArray, 2)
ReDim arrTemp1(j_lBound To j_uBound)
ReDim arrTemp2(i_lBound To i_uBound)
lngNext = 1
For i = j_lBound To j_uBound
On Error Resume Next
If SkipBlanks Then
If Transpose Then
ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Rows(i)))
Else
ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Columns(i)))
End If
End If
If Err.Number = 0 Then
k = 1
For j = i_lBound To i_uBound
If SkipBlanks Then
If InputArray(j, i) <> "" Then
arrTemp2(k) = InputArray(j, i)
k = k + 1
End If
Else
arrTemp2(j) = InputArray(j, i)
End If
Next j
arrTemp1(lngNext) = Join(arrTemp2, Delimiter)
lngNext = lngNext + 1
Else:
Err.Clear
End If
Next i
If SkipBlanks Then ReDim Preserve arrTemp1(1 To lngNext – 1)
If lngNext > 2 Then
JoinText = Join(arrTemp1, FieldDelimiter)
Else: JoinText = arrTemp1(1)
End If
If DelimitEnd And JoinText <> "" Then JoinText = JoinText & FieldDelimiter
errhandler:
End Function
I like this function. I’m sure I’ll like it even more when you’re all finished polishing it to a bright sheen.
