5 Easy Ways to Add Comma in Excel to Concatenate Cells

When data from multiple cells are gathered into another cell altogether is known as the Concatenation of cells. Generally, concatenation is done with delimiters. Delimiters like commas, hyphens, dash, etc. are used to separate the data that are gathered in a cell. But adding delimiters into concatenated cells isn’t everyone’s cup of tea. Like many of us, if you are determined to learn to concatenate cells and add a comma between them in Excel, then you are on the right track.

Nevertheless, MS Excel has also a function named CONCATENATE function to concatenate cells into another. It is also known as the CONCAT function. In this article, we are going to construe the making of concatenated cells as well as add commas between the texts in Excel. we will CONCATENATE function as well as TEXTJOIN and VBA Macro to add commas between texts.

how to add comma in excel concatenate


📁 Download Excel File

To practice please download the Excel file from here:


Learn to Add Comma in Excel to Concatenate Cells with These 5 Methods

We are going to illustrate 5 catchy methods to concatenate and add a comma in Excel. Here we consider a dataset named Officials of ALPHA Ltd. containing Designation, Name, and Region. The dataset has 4 columns as well as 9 rows. However, we will expound on the use of CONCATENATE, TRANSPOSE, and TEXTJOIN functions as well as VBA Macro to construct methods. So, let’s get started.

Dataset named Officials of ALPHA Ltd.

method

1. Concatenate Range of Cells with Comma Using Ampersand in Excel

By writing simple formula using ampersands (&), we can gather data into a cell from multiple cells. Please check out the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell E6 to get the result under the Concatenation heading.
  • Secondly, write down the following formula in the E6 cell.

=B6&”, “&C6&”, “&D6

  • Therefore, we obtain the result in Sales Manager, Jenny, Texas in the E6 cell altogether.
  • Finally, We obtain the result containing commas auto-filling the E6:E9 range.

Use of Ampersand to add comma in the concatenated cell Excel.

📕 Read More: 5 Tricks to Insert Comma Between Words in Excel

method

2. Implement CONCATENATE Function to Add Multiple Cells with Comma in a Row in Excel

CONCATENATE function is also known as the CONCAT function. Applying the CONCATENATE function, one can gather data including commas within a second. Check the general Syntax of CONCATENATE function below.

=CONCATENATE(Text1,Text2,Text3)

Please check out the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the blank cell E6 to get the result under the Concatenation heading.
  • Secondly, write down the formula in the E6 cell containing the CONCATENATE function.

=CONCATENATE(B6,”, “,C6,”, “,D6)

  • Thirdly, CONCATENATE functions directly joins the cells with or without a delimiter.
  • Therefore, we achieve the result Sales Manager, Jenny, Texas in the E6 cell altogether.
  • Finally, obtain the result in the E6:E9 range using the Fill Handle option.

Use the CONCATENATED function to gather multiple data by adding comma in a cell in Excel.

📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula

method

3. Use CONCATENATE and TRANSPOSE Functions to Add Multiple Cells in a Column with Comma

Use of the TRANSPOSE function to convert the vertical texts into horizontal texts. In addition, using the CONCATENATE function, you can add the texts or cells into a single cell.

The general Syntax of the TRANSPOSE function is,

=TRANSPOSE(Array)

Furthermore, navigate the general Syntax of CONCATENATE function below.

=CONCATENATE(Text1,Text2,Text3)

Please check out the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select the blank cell C9 to get the result under the Concatenation heading.
  • Then, input the following formula in the C9 cell having TRANSPOSE and CONCATENATE functions.

=CONCATENATE(TRANSPOSE(C5:C7)&”,”)

🔨 Formula Breakdown

👉  Firstly, TRANSPOSE(C5:C7) converts the texts of cells in a row the cell C9 results in {“Sales Manager, “, “Jenny, “, “Texas,”}.

👉  Secondly, a comma separates each cell.

👉  Thirdly, CONCATENATE function adds the cells into another cell including commas.

  • Therefore, we are unable to achieve the result, rather we get a #Value! Error in C9 cell.
  • Now clicking the C9 cell, select TRANSPOSE(C5:C7)&”,” and press the F9 key to navigate =CONCATENATE({“Sales Manager, “, “Jenny, “, “Texas,”}) in the formula bar.

Removing the 2nd bracket to concatenate the cells into another in Excel adding commas.

  • Then, remove the 2nd Bracket {} from the formula bar and it looks like =CONCATENATE(“Sales Manager, “, “Jenny, “, “Texas,”).
  • Further, press Enter to proceed.

2nd bracket was removed to concatenate and added by commas.

  • Therefore, we obtain the concatenated cell from vertical cells containing texts and result in Sales Manager, Jenny Texas in the C9 cell.
  • Finally, repeat the same procedure to get a similar result in D9, E9, and F9 cells.

Use the CONCATENATE and TRANSPOSE functions to gather multiple data by adding commas in a cell in Excel.

📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel

method

4. Utilize TEXTJOIN Function to Connect Multiple Cells with Comma

Excel allows us to connect multiple cells to another cell. To do so, you must use the TEXTJOIN function to deal with cells containing texts. However, you must use MS Excel 2019 or Higher versions to enjoy this amazing function. Please check the general Syntax of the TEXTJOIN function below.

TEXTJOIN(Delimiter, ignore_empty, text1, [text2], …)

Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell E6 to get the result under the Concatenation heading.
  • Then, write down the following formula in the E6 cell including the TEXTJOIN function.

=TEXTJOIN(“,”,TRUE,B6:D6)

  • Next, the TEXTJOIN function finds the values in cells B6, C6, and D6 cells. If it finds out values that means TRUE and a comma joins the two cells.
  • Further, we obtain the result Sales Manager, Jenny, Texas in the E6 cell altogether using the TEXTJOIN function.
  • Finally, obtain the result including commas by auto-filling the E6:E9 range.

Use TEXTJOIN to add commas in the concatenated cell Excel.

📕 Read More: 7 Easy Ways to Use Comma in Excel Formula

method

5. Apply VBA Macro to Concatenate Multiple Cells with Delimiter (,) in Excel

With a VBA code, you will be able to concatenate multiple cells with delimiters like Commas, dashes, hyphens, or colons. By getting the Developer option in the Top Ribbon, you can generate a program on your own.  Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Check first if your Developer option is enabled or not.
  • Secondly, to enable the Developer tab in the Top Ribbon, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

Right-click on the mouse to select Customize the Ribbon.

  • Thirdly, click on the Developer option and hit the OK button.

Enabling the Developer option in the Top Ribbon.

  • Now Developer mode is on.
  • Then, click on the Visual Basic feature.
  • Next, to insert the VBA code, click on Module from the Insert menu.

VBA Module to insert VBA code from the Insert menu.

Code

Sub Concatenate_Cells()
Dim A As Integer
Dim Concat As String
For A = 6 To 10
Cells(A - 1, 5).Value = Concat
Concat = Cells(A, 2).Value & ", " & Cells(A, 3).Value & "," & Cells(A, 4).Value
Next A
End Sub

🔨 Code Breakdown

👉  Firstly, set A and Concat as integer and string respectively. As Concat upbrings the value of cells under the Concatenation heading containing texts. In contrast, A is the row number of cells.

👉  Secondly, Cells(A – 1, 5).Value is the concatenated cells. Where A means, we fix the row from 6th to 10th.

👉  Thirdly, according to Concat = Cells(A, 2).Value & “, ” & Cells(A, 3).Value & “,” & Cells(A, 4).Value formula, concatenated cells will be filled inserting the values of columns B, C and D adding commas.

Excel VBA box to insert code to concatenate with commas.

  • Therefore, column E under the Concatenation heading concatenates the data from cells of columns B, C, and D.

Data of 3 columns are concatenated into cells of another column.

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel


How to Concatenate Multiple Cells with Space Using VBA Macro

Earlier, we showed you the approach to adding commas in a concatenated cell. Similarly, following the same VBA code, you will be able to insert spaces in concatenated cells. Please check out the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Like previously, open a VBA module from the Insert menu of Visual Basic for Application box.
  • Then, write down the following code in the module.
  • We have to make a little change from the previous code. Write, Concat = Cells(A, 2).Value & ” ” & Cells(A, 3).Value & ” ” & Cells(A, 4).Value instead of Concat = Cells(A, 2).Value & “, ” & Cells(A, 3).Value & “,” & Cells(A, 4).Value in the VBA code.

Code

Sub Concatenate_Cells()
Dim A As Integer
Dim Concat As String
For A = 6 To 10
Cells(A - 1, 5).Value = Concat
Concat = Cells(A, 2).Value & " " & Cells(A, 3).Value & " " & Cells(A, 4).Value
Next A
End Sub
  • Next, inserting the VBA code, click on the Run icon to execute.

Excel VBA box to insert code to concatenate with spaces.

  • Therefore, space is added while concatenating the cells and gathered data into column E under the Concatenation heading.

Space added in the concatenated cells.


📄 Important Notes

🖊️  Remember, the TEXTJOIN function is available on MS Excel 2019 or a Higher version.

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  CONCATENATE and TEXTJOIN functions require commas while constructing formulas.


📝 Takeaways from This Article

📌  General approach to concatenate multiple cells into another with symbols.

📌  CONCATENATE function to join multiple cells in the same row by commas.

📌  Combination of TRANSPOSE and CONCATENATE functions to convert vertical cells into horizontal cells from multiple columns by commas.

📌  TEXTJOIN function to add multiple cells.

📌  VBA Macro to concatenate multiple cells by a delimiter.


Conclusion

In this article, we expound on 5 handy methods to add commas and concatenate multiple cells in Excel. I hope you enjoyed your learning and will be able to concatenate multiple cells with delimiters. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.


Related Articles

(Visited 47 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo