57

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

Wasteland
  • 4,889
  • 14
  • 45
  • 91

5 Answers5

101

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    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. – Rotsiser Mho Mar 29 '18 at 05:27
0

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
Joe
  • 170
  • 9
  • 1
    If the OP has CONCAT(), then the OP has TEXTJOIN() as they came out the same time. – Scott Craner Feb 10 '17 at 17:52
  • 1
    Are you sure? I'm on my work computer and I definitely have CONCATENATE on Excel 2010 but I don't have TEXTJOIN – Joe Feb 10 '17 at 17:53
  • 1
    CONCAT<>CONCATENATE they are two different functions. – Scott Craner Feb 10 '17 at 17:54
  • 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? – Joe Feb 10 '17 at 17:55
  • 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. – Scott Craner Feb 10 '17 at 17:56
  • Thanks - I've got the latest (part of O365) – Wasteland Feb 10 '17 at 17:59
0

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.

santhosha
  • 351
  • 2
  • 8
  • 20
  • 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. – chill389cc Jan 02 '23 at 19:46
0

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
Nidhogg
  • 1
-1

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

Apostolos55
  • 574
  • 3
  • 8