Excel: How to combine all cells in a column, separated by commas?-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

You can use

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"