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.
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.
⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.
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.
⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.
Read More: How to Transpose Columns to Rows In Excel (6 Methods)
Similar Readings
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- How to Convert Single Columns to Rows in Excel with Formulas
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 (,).
⦿ Upon pressing ENTER, we will get the comma separated list of cell values of the Product column in cell C5.
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.
Step 2:
⦿ Now, click on the Insert button and select Module.
⦿ 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
Step 3:
⦿ We will now go back to the worksheet and write the following code in cell C5.
=ColumntoList(B5:B9)
⦿ Upon pressing ENTER, we will get the comma separated list with single quotes around each cell value of the Product column in cell C5.
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.
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.
⦿ Now, we will click on the Paste Options and select Keep Text Only options.
⦿ 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.
⦿ Now, we will see that all cell values in the Product column are converted to a comma separated list in Microsoft Word.
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
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Based on Criteria in Excel
- How to Transpose in Excel (5 Easy Ways)
- How to Transpose Rows to Columns in Excel (5 Useful Methods)
- VBA to Transpose Array in Excel (3 Methods)
