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.


Download Practice Workbook

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


5 Suitable Ways to Make a Comma Separated List in Excel

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.

make a comma separated list in excel


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)

Use CONCATENATE Function to Make a Comma Separated List in Excel

  • 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 (3 Easy Methods)


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

If you have access to Microsoft Excel 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)

Apply TEXTJOIN Function to Make a Comma Separated List in Excel

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

Apply TEXTJOIN Function to Make a Comma Separated List in Excel

Read More: How to Make Alphabetical List in Excel (3 Ways)


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

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

  • 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 (3 Quick Methods)


Similar Readings


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.

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

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.

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

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

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

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

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

Read More: How to Generate List Based on Criteria in Excel (4 Methods)


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

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

  • 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

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

Step 2:

  • Hence, the Make Comma Separated List module pops up. In the Make Comma Separated List module, write down the below VBA
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

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

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)

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

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

Run a VBA Code to Make a Comma Separated List 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


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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo