MS Excel - Concat with a delimiter

Question

I've got a long spreadsheet with numbers.

I need to get them in one string delimited by ; eg. 4364453;24332432;2342432

I know I can do:

=concat(A1:A2000)

but that will merge it in one string without the delimiter - I can't seem to find an option for a delimiter when you specify a range.

Thank you

Lagging

What version of Excel are you using? – Mandrel

Latest for Office365 for Education - I think it's 2016 version. – Lagging

Answer 1

Use TEXTJOIN() instead:

=TEXTJOIN(";",TRUE,A1:A2000)

---

For those who do not have OFFICE 365 Excel then use this UDF that mimics the TEXTJOIN Function.

Put this in a module attached to the workbook and use the formula above to call.

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

Dufresne

The second-to-last line should say TEXTJOIN = Left... instead of TEXTJOIN2 if you don't want the delimiter appended to the end of the text. – Unseal

Answer 2

If you're like me and your work computer has an old version of Excel that does not have TEXTJOIN, you can use a macro. Here's something quick I mocked up that will do either a single column or a single row. Just click where you want the resulting string to be placed, then run the macro.

Note that this will only handle one row or one column, like in your example data. If you wanted to do something like concatenate A1:C3 you'd need different logic.

Sub ConcatenateRange()

Dim resultCell As Range
    Set resultCell = Selection

Dim concatRange As Range
    Set concatRange = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

Dim optionalSeparator As String
    optionalSeparator = Chr(34) & InputBox("Any Separator?") & Chr(34)

Dim outputString As String
    outputString = "=TRIM(CONCATENATE("

Dim rangeSize As Integer
    rangeSize = concatRange.Columns.count + concatRange.Rows.count

For Each item In concatRange
    outputString = outputString & item.Address(RowAbsolute:=False, ColumnAbsolute:=False)

    rangeSize = rangeSize - 1

    If (rangeSize > 1) Then
        outputString = outputString & ", " & optionalSeparator & ", "
    Else
        outputString = outputString & "))"
    End If
Next

resultCell.formula = outputString

End Sub

Testify

If the OP has CONCAT(), then the OP has TEXTJOIN() as they came out the same time. – Dufresne

Are you sure? I'm on my work computer and I definitely have CONCATENATE on Excel 2010 but I don't have TEXTJOIN – Testify

CONCAT<>CONCATENATE they are two different functions. – Dufresne

Oh, pfft. Good catch, I didn't even notice that in the OP. I've never had a version of Excel that had CONCAT so my brain just mentally subbed in Concatenate. Should I delete this one or leave it up, you think? – Testify

Leave it as it is a good alternative for those that may stumble on the page later. I am updating mine with a UDF that mimics TEXTJOIN. – Dufresne

Thanks - I've got the latest (part of O365) – Lagging

Answer 3

Use TRANSPOSE function:

=TRANSPOSE(A1:A200)

Select the formula and hit F2 and you will get a long string with a default delimiter ',' which can be replaced with your required delimiter.

Cyrilcyrill

This function probably doesn't do what it might have done. Now, it creates the rows/cols that are the inverse (rows to cols, or cols to rows) of the specified range. No concatenation, no delimiter. – Fitment

Answer 4

To the answer Scott Craner I wanted to add a small improvement when we are going to concatenate a range of cells where nothing will be done and we have to skip empty values. Instead of the line that assigns the result:

  TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))

I added a check if the result is empty to return it without errors.

    If TEXTJOIN <> "" Then
        TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
        Else
        TEXTJOIN = ""
    End If

Aparri

Answer 5

a proper search would have saved you all a lot of trouble... there is a "MyConCat()", one I have written from 2012 here, simpler yet very effective...

Elseelset