Download Practice Workbook
Download the practice workbook to exercise.
The dataset contains information about stationery products. To convert the Product column into a comma separated list:
Method 1 – Convert a Column into a Comma Separated List Manually
Use a formula, the ampersand sign (&) and comma (,).
Steps:
- Enter the formula in 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.
- Press ENTER to see the result.
Read More: How to Convert Columns to Rows in Excel (2 Methods)
Method 2 – Using the CONCATENATE Function to Convert a Column into a Comma Separated List
Use the CONCATENATE function.
Steps:
- Enter the formula in C5.
=CONCATENATE("'",B5,"',", "'",B6,"',", "'",B7,"',","'",B8,"',","'",B9,"'")Formula Breakdown:
The CONCATENATE function will take strings and join them to make a large text.
- Press ENTER to see the result.
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 – Applying the TEXTJOIN Function to Convert a Column into a Comma Separated List
Use the TEXTJOIN function.
Steps:
- Enter the formula in C5.
=TEXTJOIN(",", TRUE, B5:B9)Formula Breakdown:
The TEXTJOIN function concatenates strings using a delimiter. Here, the delimiter is a comma (,).
- Press ENTER to see the result.
Read More: How to Convert Columns to Rows in Excel Based On Cell Value
Method 4 – Convert a Column into a Comma Separated List Using a VBA Macro
Step 1:
- Select Visual Basic in the Developer tab. You can also press ALT+F11 to open it.
Step 2:
- Click Insert and select Module.
- Use the following code in the module. 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:
- Go back to the worksheet and enter the following code in C5.
=ColumntoList(B5:B9)
- Press ENTER to see the result.
Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
Method 5 – Using the Find & Replace Tool to Convert a Column into a Comma Separated List
Use the Find & Replace tool.
Step 1:
- Select all the cells in the Product column except the column header.
- Right-click any of the selected cells and click Copy. You can also press CTRL+C to copy the selected cells.
Step 2:
- Paste the copied cells into a blank Word document by pressing CTRL+V.
- Click Paste Options (Ctrl) and select Keep Text Only.
- Press CTRL+H to open the Find and Replace tool.
- Enter“^p” in Find what.
- Enter “,” in Replace with.
- Click Replace All.
All cell values in the Product column are converted into a comma separated list.
Read More: Convert Columns to Rows in Excel Using Power Query
Related Articles
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- 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)
