How to Convert Column to Row with Comma in Excel (3 Ways)

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

Steps:

Enter the following formula in cell C5.

=TEXTJOIN(",",TRUE,B5:B9)

The range B5:B9 represents the cells of the Item Name column.

Press ENTER.

The Item Name column will be converted to row with comma between each Item Name as shown in the following image.

Method 2 – Applying CONCAT Function

The CONCAT function can join texts from multiple ranges and strings.

Steps:

Enter the following formula in cell C5.

=CONCAT(B5:B9&",")

Press ENTER.

You will get the following output.

Method 3 – Utilizing VBA Code

Steps:

Go to the Developer tab from Ribbon.
Choose the Visual Basic option from the Code group.

The Microsoft Visual Basic window will open.

In the Microsoft Visual Basic window, click on the Insert tab.
Choose the Module option from the drop-down.

Enter 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

Click on Save.

Press the keyboard shortcut ALT + F11 and you will be redirected to your worksheet.
Enter the following formula in cell C5.

=row_with_comma(B5:B9)

Press ENTER.

The texts from the Item Name column will be converted to row with a comma between each Item Name.

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 fix this by using the TEXTJOIN function of Excel.

Steps: