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

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. Here’s an overview of the dataset.

Method 1 – Use the CONCATENATE Function to Make a Comma-Separated List in Excel

Steps:

• Use the following formula in cell C5.
`=CONCATENATE(B5,","," ",B6,","," ",B7,","," ",B8,","," ",B9)`

• Press Enter.

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

Method 2 – Apply the TEXTJOIN Function to Make a Comma-Separated List in Excel

Steps:

• Use the following formula in cell C5.
`=TEXTJOIN(“,”, B5:B9)`

• Press Enter.

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

Steps:

• Select cell C5 and insert the following formula in the formula bar:
`=B5&""&","&""&B6&""&","&""&B7&""&","&""&B8&""&","&""&B9`

• Press Enter.

Method 4 – Use the Find & Replace Command to Make a Comma Separated List in Excel

Steps:

• Select all the cells in the Fruit column except the column header.
• Press Ctrl + C on your keyboard simultaneously to copy these cells.

• Paste the copied cells into a blank Microsoft Word document with Ctrl + V.
• You’ll get a dropdown option named Paste Options (Ctrl) on the bottom-right corner of the pasted cells.

• Click on the Paste Options and select Keep Text Only.

• Press Ctrl + H to open the Find and Replace box.
• Insert “^p” in the Find what input box.
• Enter “,” in the Replace with input box.
• Click on the Replace All button.

• All cell values in the Fruit column are converted to a comma-separated list in Microsoft Word.

• Here’s the resulting list.

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

Steps:

• Go to the Developer tab and select Visual Basic.

• A window named Microsoft Visual Basic for Applications will appear.
• Insert a module by going to Insert and selecting Module.

• In the module, insert the following 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``````

• Run the VBA by pressing the Play button or F5.

• Go back to the worksheet and use the following formula in cell C5.
`=ColumntoList(B5:B9)`

• Press Enter and you will get your desired output in cell C5.

Read More: How to Create List from Range in Excel

Things to Remember

If the Developer tab is not visible in your ribbon, you can make it visible. To do that, go to File → Option → Customize Ribbon.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

1. 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

2. 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.

Md. Abdur Rahim Rasel Jul 20, 2023 at 12:39 PM

Hello TOM DE JONGH!
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.