If you need to know how to convert column to row with comma in Excel, then this article is the ideal guide to learn it. We can complete this process manually for a small dataset. However, when the dataset grows really large, the task becomes significantly more challenging at that point. This article will explain to us 3 quick and efficient ways to convert column to row in Excel.
How to Convert Column to Row with Comma in Excel: 3 Methods
In this section of the article, we will discuss 3 simple methods to convert column to row with comma in Excel. Let’s say, we have the Product List of ABC Store as our dataset. In the dataset, we have the names of flowers that are sold in the store in the column names Item Name. Our goal is to convert this column to row with comma between each Item Name.
Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Using TEXTJOIN Function
Using the TEXTJOIN function is one of the most efficient ways to convert column to row with comma. The TEXTJOIN function returns a text string as output where the given text inputs are joined by a specified delimiter.
Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, enter the following formula in cell C5.
=TEXTJOIN(",",TRUE,B5:B9)Here, the range B5:B9 represents the cells of the Item Name column
- Following that, press ENTER.
Consequently, you will see that the Item Name column is converted to row with comma between each Item Name as shown in the following picture. It’s that simple!
2. Applying CONCAT Function
You can also apply the CONCAT function of Excel to convert column to row with comma. The CONCAT function can join texts from multiple ranges and strings.
Let’s follow the procedure discussed in the following section.
Steps:
- Firstly, apply the formula given below in cell C5.
=CONCAT(B5:B9&",")- Then, hit ENTER.
As a result, you will have the following output as demonstrated in the image below
3. Utilizing VBA Code
Utilizing the VBA Macro option is another smart way to convert column to row with comma in Excel. Here, we will create a custom function to do this. Let’s use the steps given below.
Steps:
- Firstly, go to the Developer tab from Ribbon.
- After that, choose the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic window will be available on your worksheet.
- Now, in the Microsoft Visual Basic window, click on the Insert tab.
- Then, choose the Module option from the drop-down.
- Following that, write the following code in the newly created module.
Function row_with_comma(input_range As Range)
Dim output_row
Dim input_cell As Variant
For Each input_cell In input_range
If Not IsEmpty(input_cell.Value) Then
output_row = output_row & input_cell.Value & ","
End If
Next
row_with_comma = Left(output_row, Len(output_row) - 1)
End FunctionCode Breakdown
- Firstly, we declared the function name as row_with_comma along with its input data and data type by input_range As Range.
- After that, we declared a variable output_row and another variable input_cell As Variant.
- Then, we initiated a For Next loop for each input_cell In input_range.
- Following that, we used an VBA If statement to check if the cell is non-empty or not. If the cell is non-empty then assign the output_row, input_cell, and “,” together in the output_row variable.
- Next, we ended the IF statement.
- Then, we terminated the For Next loop.
- Now, we used the Len function to determine the length of the output_row variable.
- After that, we used the Left function and returned all character all of the output_row variable except the last character so that there is no extra comma at the end of the string.
- Finally, we ended the function.
- After writing the code, click on the Save option as marked in the following image.
- Then, press the keyboard shortcut ALT + F11 and you will be redirected to your worksheet.
- Now, enter the following formula in cell C5.
=row_with_comma(B5:B9)- Next, press ENTER.
Consequently, you will see that the texts from the Item Name column are converted to row with a comma between each Item Name as shown in the following picture.
How to Convert Column to Semicolon Separated List in Excel
While working in Excel, we often need to convert columns to a list where the texts are separated by semicolons. We can do this by simply using the TEXTJOIN function of Excel. To do this, let’s follow the steps mentioned in the following section.
Steps:
- Firstly, enter the following formula in cell C5.
=TEXTJOIN(";",TRUE,B5:B9)- Then, press ENTER on your keyboard.
As a result, you will have the following output on your worksheet as shown in the following image.
Read More: How to Change Semicolon to Comma in Excel
How to Convert Column to Comma Separated List with Single Quotes
In Excel, we often have text data that are enclosed by a single quote. In this part of the article, we will learn how we can convert column to a comma separated list with single quotes. Let’s say, we have the Name of 4th Grade Students as our dataset. Our goal is to convert the Name column of the dataset to a comma separated list. Let’s follow the procedure given below.
Steps:
- Firstly, apply the formula given below in cell C5.
=TEXTJOIN(",",TRUE,B5:B9)Here, the range B5:B9 indicates the cells of the Name column.
- Following that, press ENTER.
Consequently, the Name column of the dataset will be converted to a comma separated list as demonstrated in the following picture.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
That’s all about today’s session. I strongly believe that this article was able to guide you to convert column to row with comma in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy learning!
Related Articles
- How to Put Comma in Numbers in Excel
- How to Change Comma Style in Excel
- How to Change Comma in Excel to Indian Style
- [Fixed!] Style Comma Not Found in Excel
- How to Put Comma After 2 Digits in Excel
- How to Put Comma after 3 Digits in Excel
- How to Put Comma After 5 Digits in Excel
<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
