How to Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)

In Excel, it’s quite common to combine multiple cells (cell values) into a single cell. While combining we may need to use different delimiters (a character that separates values) like comma, space, line break.

Today we will show you how to combine multiple cells into one cell separated by a comma in Excel. Before diving into the session, let’s get to know about the dataset which is the base of our examples.

Here, we have a few writers with their published books over the last three years (not promoting any books, just a random selection). Using this dataset, we will combine cell values into one single cell where commas will be the separator.

Note that, this is a basic table to keep things simple. In a real-life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Combine Multiple Cells into One Cell Separated by Comma

1. Combine Cells across Columns

Let’s say, from our dataset, we want the name of the books for a specific writer regardless of the year.

We need to combine the cells that have the name of the books. Let’s explore the procedures.

1.1. Combine Using the Ampersand Sign (&)

One of the easiest ways of combining cells is using the ampersand sign (&). All you need to do is insert an ampersand sign between the cells. Then this will combine the provided cells.

For the first writer of our dataset, if we use the ampersand, it will stand like the following one

Here, we combined the cells but values are not separated by the comma.

Ampersand (&) works as an AND operation, where tasks complete in a sequence. It returns results like the C5 value first, then the D5 value, and then the E5 value.

So, we need to write the formula in such a way that after the value from cells it inserts one comma, then combines the next cell, and repeats the process until the last cell.

Our formula will be

Here, we have inserted a comma after the cells. That’s why we needed to use a couple of additional ampersands. After a cell, we added a comma using an ampersand (&), then with that portion, we added the next cell using another ampersand (&).

We have found the cells into one cell in a combined form, where they are separated by commas.

Let’s do the same for the rest of the cells.

1.2. Combine Using the CONCATENATE Function

Excel provides several functions for combing cells. CONCATENATE is one of those functions that help us to combine multiple cells.

The CONCATENATE function joins values together. To more about the function, check this CONCATENATE article.

Now, let’s write the formula using the CONCATENATE function.

You may think, we have provided 3 cells within the function. But actually, we have provided 5 values inside CONCATENATE. Along with three cells, we have inserted two commas in the form of “, “. And we have found the result as expected.

Write the formula for the rest of the cells, or exercise the Excel AutoFill feature.

1.3. Combine Using the CONCAT Function

Continuous evaluation of Excel leads us to a function called CONCAT from the version Excel 2019.

The CONCAT function joins values supplied as references or constants. To know about the function, visit the Microsoft Support site.

Our formula for combining cells using CONCAT will be like the following one

Sounds similar to the CONCATENATE formula we used in the previous section? It should be.

Microsoft brought this CONCAT function to replace the CONCATENATE function (though it’s still available). That’s why the formula has the similarity (only change the name of the function itself). And the mechanism is the same.

We have combined the cells into one cell, where the comma is the separator.

Let’s write the formula for the rest of the cells or use the AutoFill feature of Excel.

1.4. TEXTJOIN Function to Combine Cells

Another function that combines cells is TEXTJOIN. The TEXTJOIN function joins or combines several values together. This combination can be with or without delimiter.

To know about the function, visit this TEXTJOIN article.

Now, let’s combine multiple column cells using TEXTJOIN

The first parameter of the function is for a delimiter (separator). We have inserted a comma there.

The second parameter for whether we want to ignore empty cells or not. Here we have inserted 1 that denotes that Ignore empty cells.

Then the cells that we want to combine. And we have found the result of combining multiple cells.

We can insert the cells in a form of range. Let’s observe that

This has also combined multiple cells into one cell. And as our delimiter is a comma, values as separated by the comma.

One thing to note, in our formula we have enabled Ignore empty cells. So, if there is an empty cell within the provided cells or range of cells, it will discard that and combine only the cells that have values.

Let’s write the formula for the rest of the cells.

1.5. Flash Fill to Combine Cells

Excel Flash Fill feature can be a handy tool to combine cells.

To use the feature, first of all, you need to write the cell values into a cell in a form you want to combine.

Here, we want to combine the books’ names separated by a comma. So, let’s write the names for the first one.

Then selecting the cell click the Flash Fill option from the Data Tools section from the Data tab (you can use the keyboard shortcut CTRL + E as well).

You will find the combination of cells in the result.

Keep one thing in mind, Flash Fill may sometimes fail to work if you only have the sample cell selected, in such cases, you will need to select the other cells (stores rests of the results) as well.

Note that, here we are showing comma as the separator, you can use any character as the delimiter. All you need to do is provide the character like we provided commas. 

For example, here, we have used the hyphen sign (-) as the delimiter. You can use your preferred character, so do the functions or features.

2. Combine Cells over Rows

Previously we have seen the ways of combining cells from columns, in this section, we will see the approaches for combining cells over the rows.

For example, we have rearranged our authors-books dataset.

Now the books are in a single column (but in different rows). Will combine the rows that contain the name of the books for a particular author.

You can see, we have merged the rows here. You will know the procedure from this merging rows article.

2.1. Combine Using the CONCATENATE/CONCAT Function

We can combine the rows using the CONCATENATE or CONCAT function.

If we use the CONCATENATE function, then the formula will be like the following one

You will find the rows in a combined form into another cell. And the comma is the separator.

Similarly, the CONCAT version of the formula will be

It has provided the result we were looking after.

You may find the similarity between combining the cells, column by column and row by row.

Yes, it’s similar, the change is in the cell references. The functions don’t care whether the provided cells are from multiple columns or multiple rows, it only considers the cells and combines them.

Write the formula for the rest of the cells. The more you practice, the more you get the grief. Haha!

Please keep in mind that, you can also use the other methods (Ampersand (&), TEXTJOIN) described in the earlier sections.

2.2. TRANSPOSE Function to Combine

While combining rows we can use the TRANSPOSE function, along with the CONCATENATE (CONCAT as well) or TEXTJOIN function.

The TRANSPOSE function changes the orientation of a given range or array from vertical to horizontal and vice versa. To know more about the function visit the article: TRANSPOSE.

Let’s write the formula paring up CONCATENATE and TRANSPOSE.

Mere Enter may not provide you with the desired result. There are a couple of steps to reach the result of combined cells.

Step 1: Select the TRANSPOSE portion of the formula and press F9.

You will find the cell values within curly braces. Since we have inserted a comma “, “ inside TRANSPOSE we will find the values followed by a comma.

Step 2: Remove the curly braces and hit ENTER.

Now it will combine the cells and the comma will be the separator.

Similarly, you can get the values for the rest of the cells.

If you want to pair TRANSPOSE and TEXTJOIN, then let’s assist you a bit

This is the formula and we hope that you remember the steps to get the desired result.

3. User-Defined Function to Combine Cells (VBA Code)

You can create your own function to combine cells. Sensing VBA Code!!! Yes, in this section, we will write VBA code to build a function to combine multiple cells into one.

Open the Microsoft Visual Basic for Application window, the shortcut key is ALT + F11. Then right-click on the project name and click Module from Insert.

Now insert the following code in the code window

Function Combine(WorkRng As Range, Optional Sign As String = ", ") As String

Dim Rng As Range

Dim ResultStr As String

For Each Rng In WorkRng

If Rng.Value <> " " Then

ResultStr = ResultStr & Rng.Value & Sign

End If

Next

Combine = Left(ResultStr, Len(ResultStr) - Len(Sign))

End Function

Here we have created a function, Combine. Which takes two parameters at most (Sign is an optional one). Within the function, we have declared a couple of variables; Rng as Range and ResultStr as String.

Then we used a loop to iterate through the cells within our range and forms the result in the ResultStr variable.

Notice that before inserting a value in the result, we have checked whether it’s empty or not.

Combine = Left(ResultStr, Len(ResultStr) - Len(Sign)) line of code to eradicate the delimiter for the last cell.

Now, lest’s use the function.

Here we have provided the range and the delimiter sign within the function. And it combined the cells having comma as the separator.

Since we have made it Sign parameter optional, we can use write the formula without it. And the answer will be in a form of our desire.

Write the formula for the rest of the cells.

This function is will work for the rows as well.

Conclusion

That’s all for today. We have listed several methods to combine multiple cells into one cell and separated them by a comma in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we have missed here.