…everyone complains about it, but nobody does anything about it. Well, certainly not Microsoft, anyhows. But back in 2012 Nigel Heffernan at Excellerando.Blogspot.com did: 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],[EndDelimiter],[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, and the default is also a comma. EndDelimiter puts an extra Delimiter of your choice on the end if you want one. Aesthetics only, really. The rest are explained below.
- That orange block is my data.
- Column D shows the result if you point the function at each respective row
- Row 8 shows the result of pointing the function at each respective column
- In rows 12 to 15 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. If your data happenned to be laid out like this, you wouldn’t need that Transpose argument:
The DelimitEnd argument does something pretty minor, really. If we include it, the end of the string gets padded with it – in this case an Exclamation Mark . If we exclude it, the string doesn’t get padded with any extra delimiters:
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:
A real-world example where this might be useful is when concatenating lists of names, where some may have more parts than others:
Both the last two screenshots show examples of using three different delimiters…a space between words, a comma between columns, and something different on the end.
Here’s the code and workbook:
Join Function_20141115
Public Function JoinText(target As Range, _
Optional Delimiter As String = “,”, _
Optional FieldDelimiter As String = “,”, _
Optional EndDelimiter As String = “”, _
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.
‘ ####################
‘ # Revision history #
‘ ####################
‘ Date (YYYYMMDD) Revised by: Changes:
‘ 20141114 Jeff Weir Turned into worksheet function, added FinalDelimiter and Transpose options
‘ 20141115 Jeff Weir Changed FinalDelimiter to EndDelimiter that accepts string, with default of “”
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 JoinText <> “” Then JoinText = JoinText & EndDelimiter
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.
Interesting !
Function F_snb(c00, ParamArray sp())
' sp(0)= row-delimiter; sp(1)= column-delimiter; sp(2)= skip empry cells ; sp(3)= transpose
sn = c00
If Not IsArray(sn) Then
F_snb = sn
Exit Function
End If
If sp(3) Then
For j = 1 To UBound(sn, 2)
c01 = c01 & sp(0) & Join(Application.Transpose(Application.Index(sn, 0, j)), sp(1))
Next
Else
For j = 1 To UBound(sn)
c01 = c01 & sp(0) & Join(Application.Index(sn, j))
Next
End If
If sp(2) Then
Do Until InStr(c01, sp(1) & sp(1)) = 0
c01 = Replace(c01, sp(1) & sp(1), sp(1))
Loop
End If
F_snb = Mid(c01, Len(sp(0)) + 1)
End Function
While it only provides a single delimiter, I thought readers of this blog article might find the function I posted in my mini-blog article here to be of interest…
http://www.excelfox.com/forum/f22/formatted-flexible-concatenation-function-582/
The function’s main claim to fame is that is let’s you independently specify which direction the elements of a two-dimensional range are concatenated in (across a row before advancing down to the next row or down a column before advancing to the next column) with each two-dimensional range processed independent of any other two-dimensional ranges specified along with it. It also uses the formatted value as displayed in the cell rather than the cell’s underlying value.
Hi lads. Something I forgot to mention about Nigel’s rationale for using JOIN rather than concatenation – which adds quite a bit of additional complexity to the function – is this coding note from his original function:
‘ Coding note: we’re not doing any string-handling in VBA.Strings –
‘ allocating, deallocating and (especially!) concatenating are SLOW.
‘ We’re using the VBA Join & Split functions ONLY. The VBA Join,
‘ Split, & Replace functions are linked directly to fast (by VBA
‘ standards) functions in the native Windows code.
…which is why I went for his more complex approach.
@snb: your Skip Empty Cells approach doesn’t skip the 2nd delimiter, which results in this:
I love Excel. You love Excel. . . We Excel. We love Excel. Excel loves us
…which granted may be what the user actually wants. My approach gives you both options:
It seems to me that the ‘DelimitEnd’ argument is redundant.
Do you mean ?
Function F_snb(c00, ParamArray sp())
' sp(0)= row-delimiter; sp(1)= field-delimiter; sp(2)= skip empty cells ; sp(3)= transpose
sn = c00
If Not IsArray(sn) Then
F_snb = sn
Exit Function
End If
If sp(3) Then
For j = 1 To UBound(sn, 2)
c01 = c01 & sp(0) & Join(Application.Transpose(Application.Index(sn, 0, j)), sp(1))
Next
Else
For j = 1 To UBound(sn)
c01 = c01 & sp(0) & Join(Application.Index(sn, j))
Next
End If
If sp(2) Then
For j = 1 To 0 Step -1
If sp(j) <> "" Then
Do Until InStr(c01, sp(j) & sp(j)) = 0
c01 = Replace(c01, sp(j) & sp(j), sp(j))
Loop
End If
Next
End If
F_snb = Mid(c01, Len(sp(0)) + 1)
End Function
sorry, I overlooked something:
Function F_snb(c00, ParamArray sp())
' sp(0)= row-delimiter; sp(1)= field-delimiter; sp(2)= skip empty cells ; sp(3)= transpose
sn = c00
If Not IsArray(sn) Then
F_snb = sn
Exit Function
End If
If sp(3) Then
For j = 1 To UBound(sn, 2)
c01 = c01 & sp(0) & Join(Application.Transpose(Application.Index(sn, 0, j)), sp(1))
Next
Else
For j = 1 To UBound(sn)
c01 = c01 & sp(0) & Join(Application.Index(sn, j), sp(1))
Next
End If
If sp(2) Then
For j = 1 To 0 Step -1
If sp(j) <> "" Then
Do Until InStr(c01, sp(j) & sp(j)) = 0
c01 = Replace(c01, sp(j) & sp(j), sp(j))
Loop
End If
Next
End If
F_snb = Mid(c01, Len(sp(0)) + 1)
End Function
snb: The DelimitEnd argument does something pretty minor, really. If we include it, the end of the string gets padded with the field delimiter – in this case a full stop . If we exclude it, we don’t get that final field delimiter. I was thinking of names when I included this.
I’ll change the function so that it accepts a string, so that a different delimiter other than the field delimiter can be used. (To be sure, this could easily be done by the user by concatenation).
Your revised function still results in additional field delimiters in the case where an entire row/column is empty. i.e.:
I love Excel. You love Excel. . . We Excel. We love Excel. Excel loves us
Thanks for the shout-out!
Hopefully Excellerando won’t collapse under the extra traffic; and I really should tidy up some of the older code – we all develop as developers, and it becomes all too easy to spot things we could’ve done better.
Meanwhile, one of the useful things about the Join2D function – using the delimiters found in a ADODB.Recordset.Save file – has been superseded by changes in the ADODB libraries.
Nevertheless, it’s a very fast way of streaming arrays to and from .csv files.
Also… Things that should work, but don’t: Join2D can be unrolled to spit out rows as vector arrays, and this ought to be a shoe-in for the single-step ‘append’ on a disconnected recordset:
rst.AddNew arrFields, arrRowData
However, I’ve never got this to work: ADO always raises errors about fields in the wrong order, or unexpected types. You can still populate the record’s field objects one at a time, and performance isn’t too bad; but it’s annoying to see a published syntax that doesn’t work in the wild.
To contradict the content of the title, see:
http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.15
@Nigel
Are you referring to ?
Sub M_snb()
With CreateObject("ADODB.recordset")
.Open "SELECT * FROM
sheet1$
", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0"""c00 = .GetString(, , ";", vbCrLf)
End With
End Sub
@Jeff Mid$ is faster than Join/Transpose
Function MultConc(Rng As Range, Sep As String)
Dim i As Long
Dim v As Variant
Dim pad As String
v = Rng.Value2
pad = Space$(32765)
For i = 1 To UBound(v, 1)
Mid$(pad, i + (i – 1), 2) = (v(i, 1) & “|”)
Next i
MultConc = pad
End Function
@snb: great stuff. I never let the facts stand in the way of a catchy blogpost title, though. ;-)
@sam: Hi pal. I’ll check it out. Have you timed it over a large range to see if there’s any breakeven point?
@snb – No. I’m referring to this:
All well and good in a trivial example.
Now try this:
..And I get this:
Error 3001: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
So arrays returned by the VBA.Strings.Split function have some undisclosed type information that the ADODDB recordset picks up when type-checking fields. I note that the array returned by Scripting.Dictionary.Items() is fine; it’s variant arrays, and
Dim arrRecord() As String
doesn’t fix it.I can, of course, loop through the fields and set their values one at a time: that looks sensible in the example I’m using here, but that’s not the point when I’ve got code for large data sets that already emits arrays without additional allocation or concatenation.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms677536%28v=vs.85%29.aspx
@Nigel
sn=array("..","..","..")
typename(sn) : Variant()
sn=split("..,..,..",",")
typename(sn) : String()
You can convert string() to array() using:
arrfields = Application.Index(Split("RowID,Description,ISIN", ","), 0, 0)
As with all VBA code you write, if speed is of any importance you need to test.
So I decided to compare the time it takes to run the Join function as opposed to just using a plain and simple loop.
Using this routine:
Sub Test()
Const str As String = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,m,o,p,q,r,s,t"
Dim vData As Variant
Dim lCt1 As Long
Dim lCt As Long
Dim sNew As String
vData = Split(str, ",")
TimerStart "1"
For lCt1 = 1 To 100
sNew = vbNullString
For lCt = LBound(vData, 1) To UBound(vData, 1)
sNew = sNew & vData(lCt) & ","
Next
sNew = Left(sNew, Len(sNew) - 1)
Next
TimerReport
TimerStart "2"
For lCt1 = 1 To 100
sNew = vbNullString
sNew = Join(vData, ",")
Next
TimerReport
End Sub
'(TimerStart and TimerReport are calls to a high resolution timer)
Results after running the entire sub 16 times:
1 0.003432578
2 0.000232665
Looks like looping through the array and adding the strings "manually" is about 15 times SLOWER than using Join.
But wait, we can improve. We know these are strings so lets treat them as such:
Sub Test()
Const str As String = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,m,o,p,q,r,s,t"
Dim vData() As String
Dim lCt1 As Long
Dim lCt As Long
Dim sNew As String
vData = Split(str, ",")
TimerStart "1"
For lCt1 = 1 To 100
sNew = vbNullString
For lCt = LBound(vData, 1) To UBound(vData, 1)
sNew = sNew & vData(lCt) & ","
Next
sNew = Left(sNew, Len(sNew) - 1)
Next
TimerReport
TimerStart "2"
For lCt1 = 1 To 100
sNew = vbNullString
sNew = Join(vData, ",")
Next
TimerReport
End Sub
Result:
1 0.002164019
2 0.000217416
Ratio: 10 times, slight improvement.
We can improve further.
Replacing
sNew = sNew & vData(lCt) & ","
with
sNew = sNew & (vData(lCt) & ",")
further improves the ratio to 8.3.
Conclusion: Join clearly is the winner here, but even if you're not using it, you can improve your own code by a factor of 2 just by:
- Declaring the array as a string rather than a variant
- Being smart about adding the string (first join small parts together, before joining them with the larger string).
See this excellent article about string handling in VB6: http://www.aivosto.com/vbtips/stringopt.html
Wow! That’s a big difference. Thanks, Jan Karel.
@JKP
Interesting: I wasn’t aware that there was so much to be gained from declaring vData() As String instead of as a Variant.
I had better go back and look at some of my hand-rolled XML parsers.
@Jeff, The Mid$ should be around 3 times faster than Join on large data-sets (16 K Rows)
@ Nigel: I was playing around with your Split2d function, and I would have expected Split2d(“Weir,Jeff;Heffernan,Nigel”, “,”, “;”) to return a 2×2 array if array entered in a 2×2 range, and that evaluating it would show ={Weir,Jeff;Heffernan,Nigel} in the formula bar. But what I get is a 1d array with the words Weir, Jeff, and Nigel in it. Am I missing something?
I tried to post this comment over at your actual blog, but I’m not sure if it worked…find the commenting function of Blogger confusing.
@All
After yesterday update – Excel 2016 (Professional Plus – MSI version) has 3 new functions
1) CONCAT – Finally
2) IFS
3) SWITCH
1 ) The syntax of CONCAT is = CONCAT(Text1,[Text2],…..)
Text1- accepts – String, Cell or Cells
So you can now say = CONCAT(A1:A10)
The String1 – accepts ranges as well !!
Unfortunately you don’t have a “Delimiter” as a parameter. So if you wish to introduce a delimiter then it has to be array entered as so
= CONCAT(A1:A10&” “)
2) IFS : avoids nested IF’s : IF (Logical1, Value1, [Logical2, Value2]….)
Optional Parameters are in Pairs – So you need to build the logic Top Down
3) SWITCH : is a bit different from the Database version of SWITCH
SWITCH (Expression, Value1, Result1, [Value2, Result2]……)
Sam – That’s interesting. The only trouble is I haven’t got them. According to:
https://support.office.com/en-us/article/What-s-New-and-Improved-in-Office-2016-for-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426?ui=en-US&rs=en-001&ad=US
they were included in the January update, and according to my Account-Updates, I have the latest update.
Also my Version is 16.06366.2062, which according to Wikipedia is the January update (I couldn’t find this info on the MS site).
Any ideas why I haven’t got the new functions?
@Doug- thats strange – My office version num reads 16.0.6568.2016
My Excel ver num reads 16.0.6528.1007
Also I just now noticed there are a few more gems
=TEXTJOIN(delimiter,ignore_empty,text1,[text2]….)
text1 – can be a range refrence or an array as well both 1d and 2d
There is also MAXIFS and MINIFS !!! – This is the first time I am seeing new formulas introduced in an Update – FANTASTIC
Well I’m looking at my main two PCs that I have Office 365 pro plus installed on, and one says:
16.0.6001.1054
…and the other says
16.0.6001.1061
…and apparently they are both the latest versions, according to the message that comes up if I click Update Options>Update Now.
I have different Windows OS loaded on each machine. Maybe one is Windows 8.2 and the other Windows 10? But suffice to say that I don’t have access to any of these new functions. I don’t know what this ‘MSI Version’ thing is that Sam mentions.
I find the experience around logging in to Microsoft accounts confusing to the point of being disheartening. Never mind working out exactly what plan I’m on, and what update settings I’ve selected. Someone did a blog post recently on how to enable the 2016 preview options, but I’m damned if I can find it right now, and I’m not sure if that’s all that is required to access these new functions in any case. And I seem to get taken to different places – some requiring different logons – depending on whether I click the “Manage Account” button from the Account Tab via Excel Backstage, or the “Sign in to the customer portal” link that MS send out with the monthly invoice email, or the “Office Online” portal that I get taken to if I click the “Windows Desktop” link from the Office Insider page I just found at https://products.office.com/en-us/office-insider?ms.officeurl=insider
I’m supposed to be an expert in this $#!T. But the experience just makes me feel like a complete dumbass. WTF. I repeat: WTF
Edit: So I found some info on how to enable ‘First Release’ here, which requires you to either sign on at office.com or portal.office.com depending on whether you have a personal account or a business one, as per article here. And then you’ve got to go to the Office 365 admin center. But finding out how to do this via Google is like following a trail of breadcrumbs laid through an aviary of half-starved gulls during a windstorm.
Does anyone else feel my pain?
Anyways…apparently I’m already on “first release”. Whatever the hell that means, because do I get to share in the first release of these functions? Nope. So first release it ain’t. Who’s genius idea was it to name it that, then? And reading this, it seems that you have to enroll in the Office Insider program, but again there’s completely different breadcrumbs to follow depending on whether you’re a personal customer, a business customer, or a half-starved gull.
@Jeff: I feel your pain. It is confusing as hell.