How to Concatenate Multiple Cells with Comma in Excel (4 Ways)

To get a comprehensive idea of anything at once, you may need to concatenate multiple cells and separate them by using commas. This article is talking about how to concatenate multiple cells with comma in Excel by applying some formulas, functions as well as VBA code.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Ways to Concatenate Multiple Cells with Comma in Excel

We’ll show you four different techniques to concatenate multiple cells and separate them with a comma in the sections below. To do this, we’ll utilize the CONCATENATE and TEXTJOIN functions. Later on, we’ll present another approach for accomplishing the same goal using VBA code.

Below is an example data set that will be used to complete the task.

Sample Data

1. Apply the CONCATENATE Function to Concatenate Multiple Cells with Comma in a Row

A simple way to concatenate things is to use the CONCATENATE function. To complete the work, follow the procedures given below.

Step 1:

  • Firstly, type the formula in a blank cell.
=CONCATENATE(B5:E5& “,”)

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 2:

  • Secondly, select the formula.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 3:

  • Then, press F9 to convert them in value.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 4:

  • After that, remove the curly brackets { } from the formula.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 5:

  • Finally, press Enter to see the results.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Notes.  Don’t forget to remove the curly brackets { } from the formula.

Read More: How to Concatenate Columns in Excel (8 Simple Methods)


2. Combine the CONCATENATE and TRANSPOSE Functions to Concatenate Multiple Cells with Comma in a Column

In addition to concatenating multiple cells in a row, we can do the same thing for a column. Follow the instructions below to use the concatenate operation for a column.

Step 1:

  • In cell E4, same with the first row of the column, type the following formula.
=CONCATENATE(TRANSPOSE(C4:C7)& “,”)

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 2:

  • Then, select the formula.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 3:

  • Then, press F9.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 4:

  • Remove the curly brackets { } again as we do before.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Step 5:

  • Finally, press Enter to see the results.

Apply The CONCATENATE Function to Concatenate Multiple Cells with Comma

Notes.  Remember that, you should write the formula in a separate cell in the same row as the first row of the column. As our first cell value was James Rodrigues in C4 in row 4, we enter our formula in the same row but in a different cell E4. After concatenating you can move it anywhere.

Read More: Opposite of Concatenate in Excel (4 Options)


Similar Readings:


3. Apply the TEXTJOIN Function to Concatenate Multiple Cells with Comma

You can use the TEXTJOIN function in MS Excel 365 to combine multiple cells separated by a comma into a single cell. To do that in Excel 365, follow the steps below.

Step 1:

  • Simply write the following formula.
=TEXTJOIN(",",TRUE,B5:E5)

Apply the TEXTJOIN Function to Concatenate Multiple Cells with Comma

Step 2:

  • Then, press Enter to see the result.

Apply the TEXTJOIN Function to Concatenate Multiple Cells with Comma

Notes.  The TEXTJOIN function to concatenate multiple cells feature is only available in Excel 365 subscribed users.


4. Run a VBA Code to Concatenate Multiple Cells with Comma

We can also concatenate multiple cells and use a separator comma by using the VBA code.

Follow the following procedures below.

Step 1:

  • Firstly, press  Alt  + F11  to open the VBA Macro
  • Click on the Insert tab and choose The Module
  • Save the program and press F5 to run it.

Run a VBA

Step 2:

  • Then, just paste the following VBA
Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Concate As String
For Each Cell In Ref
Concate = Concate & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Concate, Len(Concate) - 1)
End Function

Here,

  • Dim Cell As Range is declaring a variable cell as range value.
  • Dim Concate As String is declaring a variable Concatenate as a string.
  • Concate = Concate & Cell.Value & Separator is the command to join the cell value with a separator.
  • CONCATENATEMULTIPLE = Left(Concate, Len(Concate) – 1) is the command to concatenate the last concatenated cells.

Run a VBA

Step 3:

  • After that, write the following formula using the CONCATENATEMULTIPLE
=CONCATENATEMULTIPLE(B5:E5,",")

Run a VBA

Step 4:

  • Finally, press the Enter button to see the results.

Run a VBA

Read More: How to Concatenate in Excel (3 Suitable Ways)


Conclusion

To summarize, I hope you’ve gained a basic knowledge of how to concatenate multiple cells with commas from this article. These methods should all be taught and used to your data. Examine the practice book and apply what you’ve learned. We’re inspired to keep producing courses like this because of your important support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

Your queries will be answered as soon as possible by the Exceldemy team.

Stay with us and continue to learn.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo