MS Excel - Concat with a delimiter

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

4 Answers

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 

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.

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 

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...