Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Insert Comma in Excel for Multiple Rows (3 Suitable Ways)

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.


Download Practice Workbook

You can download and practice from our workbook here for free!


3 Effective Ways to Insert Comma in Excel for Multiple Rows

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 Office 365 version of Microsoft 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.

Dataset to Insert Comma in Excel for Multiple Rows


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&","

Use Ampersand (&) to Insert Comma in Excel for Multiple Rows

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

Drag Fill Handle Below to Copy Formula

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.

Inserted Commas in Excel for Multiple Rows

Read More: How to Insert Comma Between Words in Excel (4 Simple Methods)


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,",")

Insert the CONCATENATE Function to Insert Comma in Excel for Multiple Rows

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

Drag Fill Handle Below to Copy Same Formula

Consequently, you will see that you have inserted commas in Excel for multiple rows successfully. And, the result would look like this.

Inserted Commas in Excel for Multiple Rows

Read More: How to Add Comma in Excel Between Names (4 Suitable Ways)


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.

Access the 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.

Write the VBA Code in the Code window

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.

Code in the Code Window to Insert Comma in Excel for Multiple Rows

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

Go to File Tab

  • At this time, the expanded File tab will appear.
  • Subsequently, click on the Save As option.

Choose the Save As option

  • As a result, the Excel Save As window will open.
  • Now, click on the Browse option.

Excel Save As Window

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

Save the Excel File as .xlsm File to Enable Macro

  • Now, go to the Developer tab again. Subsequently, go to the Visual Basic tool.

Access the Visual Basic Tool Again

  • As a result, the Microsoft Visual Basic for Applications window will appear again.
  • Subsequently, click on the Run button.

Run the VBA Code to Insert Comma in Excel for Multiple Rows

  • At this time, the Macros window will appear.
  • Choose the Created macro from the Macro Name: options.
  • Subsequently, click on the Run button.

Choose the Macro to Insert Comma in Excel for Multiple Rows

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.

Inserted Commas in Excel for Multiple Rows

Read More: How to Separate Address in Excel with Comma (3 Easy Methods)


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.
And, visit ExcelDemy for many more articles like this. Keep learning and keep growing! Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo