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.
=CONCATENATE(B5,","," ",B6,","," ",B7,","," ",B8,","," ",B9)
- 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.
=TEXTJOIN(“,”, B5:B9)
- 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,
=B5&""&","&""&B6&""&","&""&B7&""&","&""&B8&""&","&""&B9
- 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.
=ColumntoList(B5:B9)
- 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.
Thank you for this guide, it worked perfectly for me. The instructions and step by step screenshots are clear and easy to understand. I learned a lot from this guide.
Hello, Eric!
Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.
Regards
ExcelDemy
What I’m searching for is the other way around. So I have one cell with comma separated words, and I would like excel to take all those words and create a dropdown menu (list) from that, instead of creating a dropdown list from a colomn.
Hello TOM DE JONGH!




Thanks for your query.
You have one cell with comma-separated words, and create a dropdown menu (list) from that, instead of creating a dropdown list from a column.
We will convert those comma-separated words into columns using the Text to Columns feature. Hence, transpose these words using the TRANSPOSE function. After that, we will create a drop-down list using the Data Validation feature.
Let’s follow the instructions below to learn!
To split the comma-separated words, go to,
Data >> Data Tools >> Text to Columns
We will use the comma as a delimiter.
Hence, you will be able to split the comma-separated words and then in cell B8 apply the TRANSPOSE function to transpose row to column.
=TRANSPOSE(B5:F5)
Now, we’ll select cell C8 to create the drop-down list using the Data Validation feature.
Finally, you will be able to create a drop-down menu from a comma-separated words cell.
Please download the Excel file to solve your problem and practice with it.
Creating Drop-Down List.xlsx
Now I think you can solve your problem. If you cannot solve your problem, you can contact with us via this mail [email protected]
Regards
Md. Abdur Rahim Rasel
Exceldemy Team