Note: The other languages of the website are Google-translated. Back to English
English English

How to convert column list to comma separated list in Excel?

If you want to convert a column list of data to a list separated by comma or other separators, and output the result into a cell as shown as below, you can get it done by CONCATENATE function or running a VBA in Excel.


Convert column list to comma separated list with TEXTJOIN function

The Excel TEXTJOIN function joins multiple values from a row, column or a range of cells with specific delimiter.

Note that the function is only available in Excel for Office 365, Excel 2021, and Excel 2019.

To convert column list to comma separated list, please select a blank cell, for instance, the cell C1, and type this formula =TEXTJOIN(", ",TRUE,A1:A7) (A1:A7 is the column you will convert to comma serrated list, ", " indicates how you want to separate the list). See the screenshot below:


Convert column list to comma separated list with CONCATENATE function

In Excel, CONCATENATE function can convert the column list to a list in a cell separated by commas. Please do as follow:

1. Select a blank cell adjacent to the list's first data, for instance, the cell C1, and type this formula =CONCATENATE(TRANSPOSE(A1:A7)&",") (A1:A7 is the column you will convert to comma serrated list, "," indicates the separator you want to separate the list). See screenshots below:

2. Highlight the TRANSPOSE(A1:A7)&"," in the formula, and press the F9 key.

3. Remove curly braces {and } from the formula, and press the Enter key.

Now, you can see all values in the column list been converted a list in a cell and separated by comma. See screenshot above.

Quickly convert a column list to a comma separated list with Kutools for Excel

Kutools for Excel’s Combine Columns or Rows without Losing Data utility can help Excel users easily combine multiple columns or rows into one columns/row without losing data. In addition, Excel users can wrap these combined text strings with carriage or hard return. Full Feature Free Trial 30-day!

Convert column list to comma separated list with VBA

If the CONCATENATE function is a little tedious for you, you can use a VBA to quickly convert the column list to a list in a cell.

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert column list to comma separated list

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

3. Click Run button or press F5 to run the VBA.

4. A dialog displayed on the screen, and you can select the column list you want to convert. See screenshot:

5. Click OK, then another dialog popped up for you to select a cell. See screenshot:

6. Click OK, and all values in the column list have been converted to a list separated by comma in a cell.

Tip: In the above VBA, "," indicates the separator you need, and you can change it as you need.


Convert column list to comma separated list with Kutools for Excel

You can also apply Kutools for Excel’s Combine utility to combine a column list, and separate each value with comma easily.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the column list that you will convert to a comma separated list, and click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data.

2. In the opening Combine Columns or Rows dialog box, you need to:
(1) Check the Combine rows option in the To combine selected cells according to following options section;
(2) In the Specify a separator section, check the Other separator option, and type comma , into following box;

3. Click the Ok button.

Now you will see all values in the specified column list are combined into one cell and converted to a comma separated list.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: convert column list to comma separated list in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Reverse concatenate and convert one cell (a comma serrated list) to a row/column list in Excel

Normally Excel users can apply the Text to Columns feature to split one cell to multiple columns, but there is no directly method to convert one cell to multiple rows. However, Kutools for Excel’s Split Cells utility can help you easily get it done as below screenshot shown. Full Feature Free Trial 30-day!


Relative Articles:

Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The above trick is very useful. Very good job
This comment was minimized by the moderator on the site
Concatenate works perfectly fine for a small lists but this VBA code worked a charm and saved me tons of time. =) Thank you!
This comment was minimized by the moderator on the site
Thanks for the wonderful information. It worked :)
This comment was minimized by the moderator on the site
Exactly what i was looking for. Thanks a lot for this elegant VBA-code.
This comment was minimized by the moderator on the site
This is what I am looking for. Thank you so much. Great work. :)
This comment was minimized by the moderator on the site
Thanks for the information.
This comment was minimized by the moderator on the site
Thanks for sharing the code, it was very useful. THANKS...!!!!
This comment was minimized by the moderator on the site
What causes the output to be this {4.6602853777055E+199} instead of {466028,537770,550024,etc}. It doesn't happen all of the time but it's happening now and driving me nuts. Any suggestions / fixes?
This comment was minimized by the moderator on the site
# Nichole 2015-02-02 20:12 What causes the output to be this {4.660285377705 5E+199} instead of {466028,537770, 550024,etc}. It doesn't happen all of the time but it's happening now and driving me nuts. Any suggestions / fixes? Hi Nichole, Looks like you have concatanated without a delimiter - i.e. missed out an &"," in whichever method you are using. Jon
This comment was minimized by the moderator on the site
I have the same problem. Even though I use the same VBL script for all numerical series, the output is sometimes as described above. Since I use the same script in all cases, I do not think that I missed out something.
This comment was minimized by the moderator on the site
Thanks, now i can easy convert column list to comma separated list with no space
This comment was minimized by the moderator on the site
Excellent - just what I was hoping to find. Made one of my job much easier. I believe it will handle up to 48 rows at a time so I had to 'chunk' my 350 lines down. Can you say why it will not take more than 48? Thanks again!
This comment was minimized by the moderator on the site
Very Helpful! how would you add a input box to change the comma separator?
This comment was minimized by the moderator on the site
You can also paste the column into this online in-browser utility http://convert.town/column-to-comma-separated-list
This comment was minimized by the moderator on the site
[quote]You can also paste the column into this online in-browser utility http://convert.town/column-to-comma-separated-listBy TonyPorter[/quote] This works like a charm. Thank you very much for sharing it with us. I had 3865 values and Excel weren't showing me all the values in the last line. Perhaps it's a limitation of the Excel.
This comment was minimized by the moderator on the site
really helpful post. thank you.
This comment was minimized by the moderator on the site
Very useful macro .. Clear explanation.
This comment was minimized by the moderator on the site
The VBA helped out so much! I am experiencing an issue if you try to close the pop up box or cancel it, it will show that the vba needs debugged. This also happens if the value is blank. Any ideas on how to fix this? Thanks!
This comment was minimized by the moderator on the site
The VBA helped out so much! I am experiencing an issue if you try to close the pop up box or cancel it, it will show that the vba needs debugged. This also happens if the value is blank. Any ideas on how to fix this? Thanks!
This comment was minimized by the moderator on the site
Hi All, So for a few columns this formula is great, but if you were trying to figure this out on more cloumns more than 100. putting "&" is a waste of time. For me i needed sepration through "," (comma). for that, all you need to do is to separate the file in CSV, Open it on a Notepad, Copy and paste in in Word, then Copy again from Word and paste it on Excel. Hope you like to the suggested, Have a great day. Thank you, Mayank Bhargava
This comment was minimized by the moderator on the site
Used This one: "Convert column list to comma separated list with VBA", worked perfect for converting rows (1000+) of email addresses into a combined list that my email client works well with. Just changed the "," to "; " and it was good to go. Thanks!
This comment was minimized by the moderator on the site
Absolutely magic!Thanks!
This comment was minimized by the moderator on the site
I need to get my numbers to look like this ('1234567', '1234567') instead of ("1234567", "1234567") - any ideas!?
This comment was minimized by the moderator on the site
Hi,
There are no double quotas or quotas in the conversation results with any one of methods in this article.
This comment was minimized by the moderator on the site
=SUBSTITUTE(value,CHAR(34),CHAR(39))
This comment was minimized by the moderator on the site
Can someone help in converting a cell wish Value

Football, Baseball, Cricket

into


1. Football, 2. Baseball, 3. Cricket
This comment was minimized by the moderator on the site
Hi ADMINDIVISION,
You can use the Text to Columns (Excel built-in feature) or Split Cells (of Kutools for Excel) to split the cell to three columns or rows, and then apply the Insert Bullets or Numbering feature of Kutools for Excel to quickly insert numbering for the new cells/columns/rows.

Btw, there is an article introducing several solutions to inserting bullets or numberings into cells:https://www.extendoffice.com/documents/excel/950-excel-apply-bullets-numbering.html
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations