In this tutorial, I am going to share with you 6 simple methods to combine multiple cells into one separated by a comma in Excel. You can easily apply these methods in any data set to join many cell values inside a single cell using a comma. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks.
Download Practice Workbook
You can download the practice workbook from here.
6 Simple Methods to Combine Multiple Cells into One Separated by Comma in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 4 unique columns which are Company, Product 1, Product 2, and Combined. Although we may vary the number of columns later if that is needed.
1. Using Ampersand Operator to Combine Multiple Cells
In this first method, we will see how to use the Ampersand operator in Excel to combine multiple cells into one separated by a comma. Follow the steps below for this.
Steps:
- First, go to cell E5 and insert the following formula:
=C5&", "&D5
- Now, press Enter and copy this formula down to the other cells.
So, this should give us the result that we were looking for.
2. Combine Multiple Cells into One by CONCATENATE Function
The CONCATENATE function in Excel joins multiple values and returns the result as a text value. We will use this function to combine multiple cells into one separated by a comma.
Steps:
- To begin with, double-click on cell E5 and enter the below formula:
=CONCATENATE(C5,", ",D5)
- Next, press the Enter key and copy this formula below using Fill Handle.
As you can see, the above method is also very handy for quickly combining cells with a comma.
3. Applying CONCAT Function
The CONCAT function in Excel works similarly to the previous function but it can take range references in addition to individual values. Let us see how to use this function to combine multiple cells into one separated by a comma.
Steps:
- As previously, insert the below formula inside cell E5:
=CONCAT(C5,",",D5)
- After that, press the Enter key or click on any blank cell.
- Finally, use this formula in the other cells in column E.
As a result, you should get the result that you wanted.
4. Utilizing TEXTJOIN Function
The TEXTJOIN function in Excel can join multiple values and add a delimiter between them. Below are the steps to use this function to combine multiple cells into one separated by a comma.
Steps:
- First, go to cell E5 and insert the following formula:
=TEXTJOIN(", ",1,C5,D5)
- After that, confirm the formula by pressing Enter and copy this formula down by dragging the Fill Handle.
So, the TEXTJOIN function is also very useful to combine multiple cells into one.
5. Using Excel Flash Fill Feature to Combine Multiple Cells with a Comma
Let us now see a quick way to combine multiple cells into one separated by a comma using the Flash Fill feature in Excel.
Steps:
- At first, type in the value of cells C5 and D5 using a comma in between them in cell E5.
- Now, select all the cells from E5 to E10.
- Then, click on Flash Fill under the Data Tools group of the Data Tab at the top of the screen.
- Consequently, the Flash Fill feature will identify the pattern of cell E5 and apply it to the other cells as well.
6. Using VBA to Combine Multiple Cells
If you are familiar with VBA in Excel, then you can easily combine multiple cells into one separated by a comma. Follow the steps below to do this.
Steps:
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula 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
- Now, go to cell E5 and insert the following formula:
=Combine(C5:D5,",")
- Then, press the Enter key and use the Fill Handle to do this same operation in the other cells as well.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to combine multiple cells into one separated by a comma in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
Further Readings
- Combine Text in Excel (8 Suitable Ways)
- Concatenate Numbers in Excel (4 Quick Formulas)
- How to Combine Date and Text in Excel (5 Ways)
- Concatenate Date and Time in Excel (4 Formulas)
- Opposite of Concatenate in Excel (4 Options)
- How to Concatenate Two Columns In Excel (5 Methods)
- How to Add a 1 in Front of Numbers in Excel [7 Special Tricks]
