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 Function
```

**Code 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**