It is a frequent need for us to insert commas in Excel at various cells for several purposes. Sometimes, it might happen that, you need to insert a comma in Excel for the same column but at multiple rows. Now, it would be convenient and quick if you could insert these commas at multiple rows using shortcut tricks rather than inserting them one by one. In this article, I will show you 3 effective ways to insert commas in Excel for multiple rows.
How to Insert Comma in Excel for Multiple Rows: 3 Effective Ways
Suppose, you have several rows containing some data. Now, you want to insert a comma at the end of every row. You can follow any of the ways described below to accomplish this. We have used the Microsoft 365 version of Excel here. But you don’t need to worry if you have other versions of Excel. You can use all these ways in any version of Excel. If you face any problems regarding this, please let us know in the comment section.
1. Utilize Excel Ampersand (&) Operator
The quickest and simplest way to insert a comma in Excel for multiple rows is to use the Ampersand (&) operator functionality. Follow the steps below to accomplish this.
📌 Steps:
- At the very beginning, click on the C5 cell.
- Afterward, write the following formula in the formula bar. Subsequently, hit the Enter button.
=B5&","
- Now, you have inserted a comma at the end of the B5 cell content at the C5 cell.
- Subsequently, place your cursor in the bottom right position of the cell.
- As a result, a black fill handle will appear. Drag it downward to copy the formula for all the cells below.
Thus, you can see all the cells of your data being in the same column and multiple rows have commas at the end of their data. And for instance, the outcome should look like this.
2. Use CONCATENATE Function
Another simple way to insert commas in Excel for multiple rows is to use the CONCATENATE function. Follow the steps below to do this.
📌 Steps:
- First, click on the C5 cell.
- Subsequently, insert the following formula in the C5 cell. Next, press the Enter button.
=CONCATENATE(B5,",")
- As a result, you will have the C5 cell containing the B5 cell data and a comma at the end of the data.
- Afterward, place your cursor in the bottom right position of the cell.
- When the fill handle appears, drag it below to copy the formula for all the cells.
Consequently, you will see that you have inserted commas in Excel for multiple rows successfully. And, the result would look like this.
Read More: How to Use Comma in Excel Formula
3. Apply a VBA Code to Insert Comma for Multiple Rows
Besides, you can also apply a VBA code to insert commas in Excel for multiple rows. Go through the steps below to achieve this.
📌 Steps:
- First and foremost, go to the Developer tab >> Visual Basic tool.
- As a result, the Microsoft Visual Basic for Applications window will appear.
- Now, select the sheet (Sheet 7 here) where you want to apply the code.
- After selecting the sheet, the code window will appear. Subsequently, write the following VBA code here.
Public Sub InsertComma()
Dim LastRow As Long, C As Integer, R As Long
C = 2 ' C represents the Excel column number with which you will add comma and R is the Row Excel row number from which you want to add comma.
LastRow = Cells(Rows.Count, C).End(-4162).Row
Application.ScreenUpdating = False
For R = 5 To LastRow
Cells(R, C + 1) = Cells(R, C) & "," ' Assuming column I +1 is free.
Next
Application.ScreenUpdating = True
End Sub
- After writing the code, the code window will look like this.
- Now, you need to save the file in the macro-enabled format.
- To do this, close the code window and go to the File tab from your Excel ribbon.
- At this time, the expanded File tab will appear.
- Subsequently, click on the Save As option.
- As a result, the Excel Save As window will open.
- Now, click on the Browse option.
- At this time, the Save As dialogue box will appear.
- Afterward, click on the Save as type: option and choose the Excel Macro-Enabled Workbook (*.xlsm) option from the dropdown options.
- Now, go to the Developer tab again. Subsequently, go to the Visual Basic tool.
- As a result, the Microsoft Visual Basic for Applications window will appear again.
- Subsequently, click on the Run button.
- At this time, the Macros window will appear.
- Choose the Created macro from the Macro Name: options.
- Subsequently, click on the Run button.
As a result, you will see a comma inserted in Excel for multiple rows as per the commands in the VBA code. And, the outcome will look like the figure below.
Download Practice Workbook
You can download and practice from our workbook here for free!
Conclusion
To conclude, in this article, I have shown you 3 effective ways to insert a comma in Excel for multiple rows. I would suggest you go through the full article carefully and practice thoroughly. You can also download our practice workbook from here for free. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
Related Articles
- How to Add Comma Before Text in Excel
- How to Add a Comma Between City and State in Excel
- How to Apply Excel Number Format in Thousands with Comma
- How to Insert Comma Between Words in Excel
- How to Add Comma in Excel Between Names in Excel
- How to Add Thousand Separator in Excel Formula
<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
