Combine Multiple Cells into One Separated by Comma in Excel

The sample dataset has a listing of products. We’ll combine the products from the two columns into the fourth column, separating the values by a comma.

Method 1 – Using the Ampersand Operator to Combine Multiple Cells

Steps:

Go to cell E5 and insert the following formula:

=C5&", "&D5

Press Enter and copy this formula down to the other cells.

Method 2 – Combine Multiple Cells into One with the CONCATENATE Function

Steps:

Double-click on cell E5 and enter the below formula:

=CONCATENATE(C5,", ",D5)

Press the Enter key and copy this formula down using the Fill Handle.

Method 3 – Applying the CONCAT Function

Steps:

Insert the following formula inside cell E5:

=CONCAT(C5,",",D5)

Press the Enter key.
AutoFill the formula to the other cells in column E.

Method 4 – Utilizing the TEXTJOIN Function

Steps:

Go to cell E5 and insert the following formula:

=TEXTJOIN(", ",1,C5,D5)

Hit Enter and copy this formula down by dragging the Fill Handle.

Method 5 – Using Excel Flash Fill to Combine Multiple Cells with a Comma

Steps:

Type in the expected value (contents of C5, then a comma, then the contents of D5) in cell E5.
Select all the cells from E5 to E10.

Click on Flash Fill under the Data Tools group of the Data Tab at the top of the screen.

The Flash Fill feature will identify the pattern of cell E5 and apply it to the other cells.

Method 6 – Using VBA to Combine Multiple Cells

Steps:

Go to the Developer tab and select Visual Basic.

Select Insert in the VBA window and click on Module.

Insert the code below in the new window:

Function CombineCells(WorkingRange As Range, Optional Sign As String = ", ") As String
Dim Sh_Rng As Range
Dim ResultStr As String
For Each Sh_Rng In WorkingRange
If Sh_Rng.Value <> " " Then
ResultStr = ResultStr & Sh_Rng.Value & Sign
End If
Next
CombineCells = Left(ResultStr, Len(ResultStr) - Len(Sign))
End Function