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

When working with Excel data, you may often need to transform a vertical list of items into a single, comma-separated string. Whether you’re preparing input for a database, crafting a report, or simply tidying up your worksheet, there are multiple ways to achieve this. Below, we’ll explore four optimized methods:

Convert column list to comma-separated list with formulas

• Formula 1: TEXTJOIN function (Excel 2019 and later versions, Office 365)
• Formula 2: CONCAT and IF functions (All Excel versions)

Convert column list to comma-separated list with Kutools for Excel

Convert column list to comma-separated list with VBA code

Convert column list to comma-separated list with formulas

Excel doesn’t include a column-to-comma list function, but you can achieve it easily: use TEXTJOIN in Office 365/2019+, or CONCAT+IF in any version to streamline your data.

Formula 1: TEXTJOIN function (Excel 2019 and later versions, Office 365)

As Excel's most advanced concatenation function, TEXTJOIN streamlines the process with its dual functionality - flexible delimiter selection and automatic empty cell management - providing the simplest solution for contemporary Excel users.

1. Select the cell where you want the comma-separated list to appear, enter the following formula:

=TEXTJOIN(", ", TRUE, A2:A10)

2. Press Enter to get the comma-separated value.

=TEXTJOIN(", ", TRUE, UNIQUE(A2:A10))

Formula 2: CONCAT and IF functions (All Excel versions)

Before TEXTJOIN, you could combine CONCAT with IF function to assemble a list and then strip off the leading delimiter.

1. Select the cell where you want the comma-separated list to appear, enter the following formula:

=CONCAT(IF(A2:A10<>"", A2:A10 & ", ", ""))

2. Press Ctrl + Shift + Enter keys together to get the result.

Convert column list to comma-separated list with Kutools for Excel

Kutools for Excel includes a powerful Combine Rows, Columns or Cells without Losing Data utility that lets you merge the contents of an entire column into a single cell in just a few clicks. By selecting your source range and specifying a delimiter—whether it’s a comma, semicolon, space, or any custom symbol—you can instantly produce a consolidated, delimited list without manually copying and pasting each item. The tool also offers options to ignore blank cells, preserve original formatting, making it an ideal solution for quickly transforming vertical data into tidy, comma-separated lists.

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 into single cell 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 ", ".
• (3.) Choose how to handle the original cells data. (You can keep the contents of the original cells, or delete the contents from the original cells.)
• (4.) Specify the output cell, under the Place the results to section, select Other Cell option and click to select a cell to output the result.
• (5.) Finally, click OK button.
  

The result is inserted in your specified target cell.

Convert column list to comma-separated list with VBA code

Alternatively, if you’d rather automate the task or need to handle larger, dynamically changing ranges, you can write a VBA macro that loops through each cell in your specified column, appends its non-empty value followed by a comma (or any delimiter you choose), trims off the extra delimiter at the end, and then outputs the fully concatenated, comma-separated string into a target cell.

1. Hold  Alt and F11 keys 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()
    ' Updateby Extendoffice
    Dim rng As Range
    Dim InputRng As Range, OutRng As Range
    Dim outStr As String
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Select source range:", xTitleId, InputRng.Address, Type:=8)
    Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
    outStr = ""
    For Each rng In InputRng
        If Len(Trim(rng.Value)) > 0 Then
            If outStr = "" Then
                outStr = rng.Value
            Else
                outStr = outStr & ", " & rng.Value
            End If
        End If
    Next rng
    OutRng.Value = outStr
End Sub

3. Click Run button or press F5 to run the VBA. A dialog displayed on the screen, and you can select the column list you want to convert. See screenshot:

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

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

Conclusion:

• For simplicity and performance, use TEXTJOIN in Excel 2019/365.
• For compatibility, the CONCAT + IF array method works in all versions.
• For one-off tasks without formulas, Kutools delivers a point-and-click solution.
• For full automation or complex rules, a VBA macro is the most flexible.

Choose the method that aligns with your Excel version, frequency of the task, and comfort with add-ins or VBA. With these optimized techniques in your toolkit, converting any column into a neat, comma-separated list becomes a breeze. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

Related Articles:

• Convert month name to number in Excel
• Convert military time to standard time
• Convert one cell to multiple cells/rows in Excel