String.Join in Excel
Tags: programming, software, rant, picture
Published: 2022-11-09 20:42:01

---
title: String.Join in Excel
published: true
date: 2022-11-09 20:38:34 UTC
tags: programming,software,rant,picture
canonical_url: https://siderite.dev/blog/stringjoin-in-excel/
---

  Original post at: https://siderite.dev/blog/stringjoin-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](https://learn.microsoft.com/en-us/dotnet/api/system.string.join "Concatenates the elements of a specified array or the members of a collection, using the specified separator between each element or member.") or in JavaScript you get the [array join](https://www.w3schools.com/jsref/jsref_join.asp "JavaScript 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](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c "combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.").

  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)](https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f "Create custom functions in Excel"), one specified in this lovely and totally underrated Stack Overflow answer: [MS Excel - Concat with a delimiter](https://stackoverflow.com/a/42165195/4572240 "For those who do not have OFFICE 365 Excel then use this UDF that mimics the TEXTJOIN Function.").

  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  
 ![ExcelModuleInsert.jpg](https://siderite.dev/Posts/files/ExcelModuleInsert_638036231148187628.jpg)
- 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](https://docs.fileformat.com/spreadsheet/xlsm/ " type of Spreadsheet files that support Macros. From application point of view, a Macro is set of instructions that are used for automating processes. A macro is used to record the steps that are performed repeatedly and facilitates performing the actions by running the macro again. Macros are programmed with Microsoft’s Visual Basic for Applications (VBA) from within the Excel Workbook using the Visual Basic Editor and can be run/debug directly from there.")

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](https://learn.microsoft.com/en-us/office/dev/add-ins/develop/develop-add-ins-visual-studio "Develop Office Add-ins with Visual Studio")? 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!