Concatenate data in different cells and add a comma

Hello all,

I had data in column A like so:

689
805
806
807
808
809
1281
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426


I have then used a transpose function to put it in a row: 689 805 806 807 808 809 1281 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426


Now I want to use a concatenate function to have a comma between each number. Is there a suggestion on how to do this please?

Thank you!

Using Power Query/ Get and Transform found on the Data Tab

Excel 2016 (Windows) 32 bit

	A
1	Merged
2	689,805,806,807,808,809,1281,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425

Sheet: Sheet2

Mcode to get this result

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

Hi Alan,

Looks great, will try and get my head round this more. Thank you!

Hi,

use power query will be straight forward

Please Login or Register  to view this content.

or

in any cell, type =TRANSPOSE(A1:A5)&",", then press F9,
Remove the curly brackets from both ends.
Add =CONCATENATE( at the beginning of the text and end it with a round bracket).
Press Enter

or


if you are using O365, can use =TEXTJOIN(",",TRUE,A1:A24)