Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 CONCATENATE function

Convert column list to comma separated list with VBA

Convert column list to comma separated list with Kutools for Excel

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

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

Kutools for Excel’s Combine Columns or Rows 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 60-day!

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial

arrow blue right bubble 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 screenshot 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.

arrow blue right bubble 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.

arrow blue right bubble 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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Go to Download
Free Trial 60 daysPurchase
PayPal / MyCommerce

1. Select the column list that you will convert to a comma separated list, and click Kutools > Combine.

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 Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!

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 60-day!

Relative Articles:

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ashlee · 1 years ago
    I need to get my numbers to look like this ('1234567', '1234567') instead of ("1234567", "1234567") - any ideas!?
    • To post as a guest, your comment is unpublished.
      Mike Ranginson · 5 months ago
      =SUBSTITUTE(value,CHAR(34),CHAR(39))
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      There are no double quotas or quotas in the conversation results with any one of methods in this article.
  • To post as a guest, your comment is unpublished.
    Joe sagar · 3 years ago
    Absolutely magic!Thanks!
  • To post as a guest, your comment is unpublished.
    AliciaB · 3 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Mayank Bhargava · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    JohnRIII · 3 years ago
    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!
English English
en Englishaf Afrikaanssq Shqipar العربيةhy Հայերենaz Azərbaycan dilieu Euskarabe Беларуская моваbg Българскиca Catalàzh-CN 简体中文zh-TW 繁體中文hr Hrvatskics Čeština‎da Dansknl Nederlandset Eestitl Filipinofi Suomifr Françaisgl Galegoka ქართულიde Deutschel Ελληνικάht Kreyol ayisyeniw עִבְרִיתhi हिन्दीhu Magyaris Íslenskaid Bahasa Indonesiaga Gaeligeit Italianoja 日本語ko 한국어lv Latviešu valodalt Lietuvių kalbamk Македонски јазикms Bahasa Melayumt Malteseno Norsk bokmålfa فارسیpl Polskipt Portuguêsro Românăru Русскийsr Српски језикsk Slovenčinasl Slovenščinaes Españolsw Kiswahilisv Svenskath ไทยtr Türkçeuk Українськаur اردوvi Tiếng Việtcy Cymraegyi יידיש