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...
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...
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
