Extract list of cells from one column in to one cell and separate by a comma

[JTH94BT]

I have a list of approximately 13000 entries in one column. I'd like to extract this entire column and put in to one cell and separate each cell with a comma.

If there was a small number of entries in this column I'd simply do the following formula: =A1&","&A2&","&A3,&","A4 etc but the sheer number of entries would take forever just to write the formula. Is there a quicker way?

See below example.

List of entries:

S0205105

S0205125

S0205264

S0205265

S0205274

Desired outcome

S0205105,S0205125,S0205264,S0205265,S0205274

 

[Joe4]

You can use the new TEXTJOIN function:

=TEXTJOIN(",",TRUE,A1:A5)

See: TEXTJOIN function - Microsoft Support

 

[Peter_SSs]

A cell in Excel can only hold 32,767 characters. If in one cell, your data would be about 117,000 characters.

@Joe4
TEXTJOIN also has a limit of 32,767 characters

 

[JTH94BT]

You can use the new TEXTJOIN function:

=TEXTJOIN(",",TRUE,A1:A5)

See: TEXTJOIN function - Microsoft Support

thank you this worked a treat! I did have to do the calculation several times as I had too many characters in the entire list but it definitely saved me a lot of time! thanks again!

 

[Joe4]

You are welcome.
Glad we could help!
(and thanks to Peter for pointing out the limitations!)

 

[JTH94BT]

You are welcome.
Glad we could help!
(and thanks to Peter for pointing out the limitations!)

Yes thank you Peter! The #CALC would've halted me if it wasn't for your insight! This is a great forum thanks to you guys

 

[Peter_SSs]

I did have to do the calculation several times as I had too many characters in the entire list

So, are you saying that you had to display the results in several cells rather than just one?

 

[JTH94BT]

So, are you saying that you had to display the results in several cells rather than just one?

Yes due to the character limitations for one cell I split my 13000 entries in to several formulas i.e. TEXTJOIN(",",TRUE,A1:A2000) then TEXTJOIN(",",TRUE,A2001:A4000) and so on until I was able to cover all of my entries. Thanks again for your help!

 

[Peter_SSs]

OK, thanks for the clarification.