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.

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

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.

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

📕 Read More: 5 Tricks to Insert Comma Between Words 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.

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.

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

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

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,

Furthermore, navigate the general Syntax of CONCATENATE function below.

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.

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

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

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

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

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.

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.

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

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

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.

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

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

• Now, please insert the following VBA code to concatenate into a cell from multiple cells.
• Then, hit the Run button to proceed.

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

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

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

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

📄 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

• 9 Tricks to Put Comma After 2 Digits in Excel
• 2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel
• 6 Easy Ways to Put Comma After 5 Digits in Excel
• 2 Ways to Bold Text in Concatenate Formula in Excel