How to Make a Comma Separated List in Excel (5 Methods)

While working with large Microsoft Excel, sometimes we need to make a comma-separated list. We can easily do that by using Excel formulas and VBA Macros also. This is an easy and time-saving task also. Today, in this article, we’ll learn five quick and suitable ways to make a comma-separated list in Excel effectively.

How to Make a Comma Separated List in Excel: 5 Suitable Ways

Let’s assume a scenario where we have an Excel file that contains information about various types of fruits. These fruits are listed in the column titled Fruit in that Excel worksheet. We will make this column of fruits into a comma-separated list using the CONCATENATE, TEXTJOIN functions, Find & Replace and VBA Macros also. Here’s an overview of the dataset for our today’s task.

1. Use CONCATENATE Function to Make a Comma Separated List in Excel

You can use the CONCATENATE function in Excel to make a comma-separated list. We have to do the following.

Steps:

• First, we have to write down the below formula in cell C5.

• After typing the CONCATENATE function in the Formula bar, simply press ENTER on your keyboard and you will be able to make a comma-separated list which has been given in the below screenshot.

Read More: How to Make a To Do List in Excel

2. Apply TEXTJOIN Function to Make a Comma Separated List in Excel

If you have access to Microsoft 365, you can use the TEXTJOIN function to join the cell values of a column or range to make a comma-separated list.

Steps:

• First, we have to write down the below formula in cell C5.

• While typing the TEXTJOIN function in cell C5, simply press ENTER on your keyboard and you will be able to make a comma-separated list which has been given in the below screenshot.

Read More: How to Make Alphabetical List in Excel

3. Use a Custom Formula to Make a Comma Separated List in Excel

We can use our own formula using only the ampersand sign (&) and comma (,) to make a comma-separated list with sing quotes around the cell values. We have to follow the below instructions to learn!

Steps:

• First of all, select cell C5, and type the below formula in the Formula bar. The formula in the Formula bar is,

• Upon pressing ENTER, we will get the comma-separated list of the Fruit column in cell C5.

Read More: How to Make a List within a Cell in Excel

4. Perform Find & Replace Command to Make a Comma Separated List in Excel

We can also use the Find & Replace tool in Microsoft Office to make a column in Microsoft Excel into a comma-separated list in Microsoft Office. Just follow the below steps.

Step 1:

• First, select all the cells in the Fruit column except the column header.
• Hence, press Ctrl + C on your keyboard simultaneously to copy these cells.

Step 2:

• Hence, we will now paste the copied cells into a blank Microsoft Word document by pressing CTRL+V.
• Then, we will see a dropdown option named Paste Options (Ctrl) on the down-right corner of the pasted cells.

• Now, we will click on the Paste Options and select Keep Text Only options.

• Next, we will press CTRL+H simultaneously to open the Find and Replace First, we will insert “^p” in the Find what input box. Then, we will enter “,” in the Replace with input box. Finally, we will click on the Replace All button.

• Now, we will see that all cell values in the Fruit column are converted to a comma-separated list in Microsoft Word.

• After completing the above process, you will be able to make a comma-separated list which has been given in the below screenshot.

Read More: How to Generate List Based on Criteria in Excel

5. Run a VBA Code to Make a Comma Separated List in Excel

Now I’ll show how to make a comma-separated list in Excel by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will make a comma-separated list. Let’s follow the instructions below to make a comma-separated list!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Make Comma Separated List will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Make Comma Separated List module pops up. In the Make Comma Separated List module, write down the below VBA code.

Function ColumntoList(ColRange As Range)
            Dim ListOutput
            Dim cell As Variant
            For Each cell In ColRange
            If Not IsEmpty(cell.Value) Then
            ListOutput = ListOutput & "'" & cell.Value & "',"
            End If
            Next
            ColumntoList = Left(ListOutput, Len(ListOutput) - 1)
End Function

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 3:

• After running the VBA code, we will now go back to the worksheet and write the following code in cell C5.

• Further, press Enter on your keyboard and you will get your desired output in cell C5.

Read More: How to Create List from Range in Excel

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all of the suitable methods mentioned above to make a comma-separated list will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Make a Numbered List in Excel
• How to Make a Bulleted List in Excel
• Create a Unique List in Excel Based on Criteria
• How to Make a Price List in Excel
• How to Create a Contact List in Excel

<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel