String.Join in Excel
- Posted in
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!
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 Function
Hope it helps!