String.Join in Excel
There is a common task in Excel that seems should have a very simple solution. Alas, when googling for it you get all these inexplainable crappy "tutorial" sites that either show you something completely different or something that you cannot actually do because you don't have the latest version of Office. Well, enough of this!
The task I am talking about is just selecting a range of values and concatenating them using a specified separator, what in a programming language like C# is string.Join or in JavaScript you get the array join function. I find it very useful when, for example, I copy a result from SQL and I want to generate an INSERT or UPDATE query. And the only out of the box solution is available for Office 365 alone: TEXTJOIN.
You use it like =TEXTJOIN(", ", FALSE, A2:A8) or =TEXTJOIN(", ", FALSE, "The", "Lazy", "Fox"), where the parameters are:
- a delimiter
- a boolean to determine if empty cells are ignored
- a series or text values or a range of cells
But, you can have this working in whatever version of Excel you want by just using a User Defined Function (UDF), one specified in this lovely and totally underrated Stack Overflow answer: MS Excel - Concat with a delimiter.
Long story short:
- open the Excel sheet that you want to work on
- press Alt-F11 which will open the VBA interface
- insert a new module
- paste the code from the SO answer (also copy pasted here, for good measure)
- press Alt-Q to leave
- if you want to save the Excel with the function in it, you need to save it as a format that supports macros, like .xlsm
And look at the code. I mean, it's ugly, but it's easy to understand. What other things could you implement that would just simplify your work and allow Excel files to be smarter, without having to code an entire Excel add-in? I mean, I could just create my own GenerateSqlInsert function that would handle column names, NULL values, etc.
Here is the TEXTJOIN mimicking UDF to insert in a module:
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End FunctionHope it helps!
