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

`=C5&D5&E5 `

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

`=C5&", "&D5&", "&E5`

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.

`=CONCATENATE(C5,", ",D5,", ",E5)`

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

`=CONCAT(C5,", ",D5,", ",E5)`

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

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

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

`=TEXTJOIN(", ",1,C5:E5)`

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

`=CONCATENATE(C4,", ",C5,", ",C6)`

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

`=CONCAT(C4,", ",C5,", ",C6)`

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

`=CONCATENATE(TRANSPOSE(C4:C6&", "))`

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

`=TEXTJOIN(", ",1,TRANSPOSE(C4:C6))`

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.