Convert excel column to comma separated list

Question

In Excel, I have a column which has 50 rows. Now I want to convert these 50 rows into a single cell with comma separated values. Is there a way to do it?

Answer 1

The CONCATENATE function in Excel can transform a column list into a list separated by commas in a cell. Please follow these instructions:

1. Type the formula =CONCATENATE(TRANSPOSE(A1:A7)&",") in a blank cell adjacent to the list's initial data, for example, cell C1. (The column A1:A7 will be converted to a comma-serrated list, and the separator "," will be used to separate the list.) Take a look at the images below:

2. Press the F9 key to highlight the TRANSPOSE(A1:A7)&"," in the formula.

3. Remove the curly brackets and press the Enter key from the formula.

Comments

Mine definitely doesn’t do this. It instead adds a semicolon and keeps the quotation marks in the list.

---

Very useful stuff, Works great. but my requirement is to get the result separated with comma and single quotation mark like = 'Volleyball','Basketball','Football',...

---

Thank you!! this is super helpful, but i have about 350+ rows that i am trying to apply this to, and its giving me an error.  Any suggestions?

---

After I use this formula, how can I convert the results into text keeping the commas inbetween.

Answer 2

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

Answer 3

=TEXTJOIN(",",TRUE,A1:A50)--Column

=TEXTJOIN(",",TRUE,A1:AX1)--Column

Answer 4

You can Directly use =TEXTJOIN(",",TRUE,A1:A61) WHERE your Column range will be 1 to 50