How to Convert Column to Comma Separated List in Excel


This post will guide you how to convert column list into a comma separated list in one cell in Excel. How do I convert a column into a comma separated list with a formula in Excel. Or how to convert a column into comma delimited list in Excel.

1. Convert Column to Comma Separated List in Excel

Assuming that you have a list of data in range B1:B6, and you want to convert all values in column B to comma separated list in another cell. How to achieve it.

You can create a formula based on the TEXTJOIN function to achieve the result. Just like this:


Type this formula into a blank cell and press Enter key in your keyboard.

2. Convert Column to Comma Separated List Using VBA Code

You can also use an Excel VBA macro to quickly convert column to a comma separated list in Excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

convert column5
Sub ConvertColumn()
    Dim oneCell As Range, sRange As Range, dRange As Range
    myStr = ""

    Set sRange = Application.Selection
    Set sRange = Application.InputBox("select source Range:", "ConvertColumn", sRange.Address, Type:=8)
    Set dRange = Application.InputBox("select one single cell as destination cell:", "ConvertColumn", Type:=8)
    For Each oneCell In sRange
        If myStr = "" Then
            myStr = oneCell.Value
            myStr = myStr & "," & oneCell.Value
        End If
        dRange.Value = myStr
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

convert column4

#6 select the source range, such as: B1:B6.

convert column1

#7 select one blank cell to place the list that is separated by comma. click Ok button.

convert column2

#8 lets see the result.

convert column3

3. Video: Convert Column to Comma Separated List

This Excel video tutorial, where we’ll explore two methods to convert a column to a comma-separated list. We’ll start by using a formula based on the TEXTJOIN function, followed by a VBA code approach.

Leave a Reply