You can use the following formula in Excel to convert a column of values into a comma separated list:
=TEXTJOIN(", ", TRUE, A2:A11)
This particular formula converts the values in the range A2:A11 into a comma separated list.
The following example shows how to use this formula in practice.
Example: How to Convert Column into Comma Separated List in Excel
Suppose we have the following column of basketball team names in Excel:
Suppose we would like to convert this column of values into a comma separated list.
We can type the following formula into cell C2 to do so:
=TEXTJOIN(", ", TRUE, A2:A11)
The following screenshot shows how to use this formula in practice:
Notice that all of the values from column A have now been converted into a comma separated list.
How This Formula Works
Recall the formula that we used to convert the column of values into a comma separated list:
=TEXTJOIN(", ", TRUE, A2:A11)
This formula uses the TEXTJOIN function, which uses the following basic syntax:
TEXTJOIN(delimiter, ignore_empty, text1, text2, text3…)
where:
- delimiter: The delimiter to use when combining text
- ignore_empty: Whether or not to ignore empty cells
- text1, text2, text3, …: The text values to be combined
We used this particular function to combine each team value in the range A2:A11 by using a comma as a delimiter and by using the argument TRUE to ignore any empty cells in the range.
Note: You can find the complete documentation for the TEXTJOIN function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Split Word into Individual Letters
Excel: How to Extract Text Between Two Characters
