1

I have a spreadsheet with 100+ entries. One column consists of IDs. I need to combine these into a single string with each ID separated by a comma, eg:

| ID |

|123| |567| |890|

Becomes one cell with the value

123,567,980

I know I can concat each cell, but there are hundreds.

Is there a way I can concat the entire column, but separate it by commas?

Closes I've gotten is:

=CONCAT(A:A)

but I can't figure out how to add a separator so it just outputs:

123567980
JvdV
  • 70,606
  • 8
  • 39
  • 70
MeltingDog
  • 14,310
  • 43
  • 165
  • 295

2 Answers2

4

You can use TEXTJOIN :

=TEXTJOIN(",",TRUE,A1:A3)
' Output : 123,456,789

enter image description here

Or with a custom function using VBA :

=ConcatWithSep(A1:A3,",")

Function ConcatWithSep(Ref As Range, Sep As String) As String

Dim Row As Range
Dim Output As String

For Each Row In Ref
Output = Output & Row.Value & Sep
Next Row

ConcatWithSep = Left(Output, Len(Output) - 1)

End Function

Another solution with PowerQuery using Table.Transpose and Table.CombineColumns :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}, "en-US"),{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"
Timeless
  • 22,580
  • 4
  • 12
  • 30
3

TEXTJOIN function is a better alternative. You can add a separator within double quotes, for example, ","

So as per your problem, you can write the formula as

=TEXTJOIN(",", TRUE, starting cell: end cell)
MAYANK SHARMA
  • 307
  • 1
  • 4
  • 12