How to Convert Column to Comma Separated List With Single Quotes

While working in Excel, we might sometimes need to convert a column or a range to a comma separated list with single quotes around each of the cell values. In this article, I will show you how to convert a column to a comma separated list with single quotes around each of the cell values using functions like CONCATENATE, TEXTJOIN along with VBA Macro, and Find and Replace Tool.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


5 Methods on How to Convert Column to Comma Separated List With Single Quotes

Let’s assume a scenario where we have an Excel file that contains information about various stationery products. These products are listed in the column titled Product in that Excel worksheet. We will convert this column of products to a comma separated list. The image below shows the worksheet with the comma separated list of products with single quotes around each of them.

Convert Column to Comma Separated List With Single Quotes


Method 1: Convert Column to Comma Separated List Manually

We can use our own formula using only the ampersand sign (&) and comma (,) to convert the column to a comma separated list with sing quotes around the cell values. We have to follow the below steps.

Steps:

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

="'"&B5&"'"&","&"'"&B6&"'"&","&"'"&B7&"'"&","&"'"&B8&"'"&","&"'"&B9&"'"

Formula Breakdown:

The ampersand sign (&) will join the single quotes (‘’) and the commas (,) with the cell values to create a comma separated list with single quotes.

Convert Column to Comma Separated List Manually

⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.

Convert Column to Comma Separated List Manually

Read More: How to Convert Columns to Rows in Excel (2 Methods)


Method 2: Use the CONCATENATE Function to Convert Column to Comma Separated List

You can also use the CONCATENATE function in Excel to convert the column to comma separated list with single quotes. 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,"'")

Formula Breakdown:

The CONCATENATE function will take several pieces of text or strings and join them to make one large text.

Use the CONCATENATE Function to Convert Column to Comma Separated List

⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.

Use the CONCATENATE Function to Convert Column to Comma Separated List

Read More: How to Transpose Columns to Rows In Excel (6 Methods)


Similar Readings


Method 3: Apply the TEXTJOIN Function to Convert Column to Comma Separated List

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(",", TRUE, B5:B9)

Formula Breakdown:

The TEXTJOIN function concatenates or joins several pieces of text or string using a delimiter. In this example, the delimiter is a comma (,).

Apply the TEXTJOIN Function to Convert Column to Comma Separated List

Note: The TEXTJOIN function is exclusive only to be used in Microsoft Excel 365, Excel 2020, or Excel 2019. You should have access to any one of these to use the function.

⦿ Upon pressing ENTER, we will get the comma separated list of cell values of the Product column in cell C5.

TEXTJOIN Function

Read More: How to Convert Columns to Rows in Excel Based On Cell Value


Method 4: Convert Column to Comma Separated List Using VBA Macro

If you are familiar with the VBA macro in Excel, then you can use VBA to efficiently convert the column to a comma separated list with single quotes. We have to follow the below steps.

Step 1: 

⦿ First, we will select Visual Basic from the Developer tab. We can also press ALT+F11 to open it.

Convert Column to Comma Separated List Using VBA Macro

Step 2:

⦿ Now, click on the Insert button and select Module.

Convert Column to Comma Separated List Using VBA Macro

⦿ Write down the following code in the window that appears. We will press CTRL+S to save the 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

ColumntoList VBA Function

Step 3:

⦿ We will now go back to the worksheet and write the following code in cell C5.

=ColumntoList(B5:B9)

ColumntoList VBA Function

⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.

Convert Column to Comma Separated List Using VBA Macro

Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)


Method 5: Use the Find & Replace Tool to Convert Column to Comma Separated List

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

Step 1: 

⦿ First, select all the cells in the Product column except the column header.

⦿ Then, right-click on any of the selected cells. You will see the context menu. Click on Copy from the menu.

⦿ Alternatively, you can press CTRL+C to copy the selected cells.

Use the Find & Replace Tool to Convert Column to Comma Separated List

Step 2: 

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

Paste Options (Ctrl)

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

Use the Find & Replace Tool to Convert Column to Comma Separated List

⦿ Next, we will press CTRL+H to open the Find and Replace tool.

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

 Find & Replace

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

Use the Find & Replace Tool to Convert Column to Comma Separated List

Read More: Convert Columns to Rows in Excel Using Power Query


Quick Notes

🎯 If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.

🎯 To open the VBA editor Press ALT + F11. And you can press ALT + F8 to bring up the Macro window.


Conclusion

In this article, we have learned how to convert a column or a range to a comma separated list with single quotes around each of the cell values. I hope from now on you can convert a column to a comma separated list with single quotes very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo