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

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