How to Add Comma in Excel (10 Easy ways)

This article will explore how to add comma in Excel between numbers and names and use various functions to add commas in the cell.

One common formatting technique is adding commas to numbers, which can make large numerical values easier to read and understand. Whether you need to format monetary values, large quantities, or any other numerical data, Excel provides a straightforward method for adding commas. To get a better visualization of big numerical values, we should delve into how to add commas in Excel in this article.

Download Practice Workbook

How to Add Comma in Excel: 10 Easy Methods

1. Adding Ampersand (&) Operator

• We have used Ampersand (&) to attach the data from the B5 and C5 cells and insert a comma between them. The formula is:

• After using the above formula in D5 cell, we press Enter and apply Fill Handle to copy the formula down to the D10 cell.

So, the output will be as follows.

2. Using the CONCATENATE Function to Add Comma

• We have used the CONCATENATE function to combine the data from B5 and C5 and inserted a Comma between them.

3. Applying SUBSTITUTE Function to Replace Other Characters with Comma

• We have used the SUBSTITUTE function to substitute the “-” of the D5 cell with “,”.

4. Using the FIXED Function to Add Comma After 3 Digits

• The FIXED function puts a comma after 3 digits. Here, we do not have control over it. The formula is given below:

5. Using TEXTJOIN Function to Combine a List with Comma

• The TEXTJOIN function puts a comma between B5 and C5. The formula is given below:

🔎 Formula Explanation

• TEXTJOIN: It is an Excel function used to join multiple text strings together.
• “,”: This is the delimiter or separator that will be used to separate the text values. In this case, it’s a comma (“,”).
• TRUE: This parameter specifies that empty cells should be ignored. If a cell is empty, it will not be included in the final text string. /wpsm_box]

6. Using the Format Cells Option to Put Comma

• First, select the E column and right-click on it.
• Choose the Format Cells option from the Context Menu.

• Now we have to select the Number from the Format Cells window and check Use 1000 Separator (,).
• Then press OK.

• Moreover, you can use Custom Number Format and customize your own rule for inserting Commas.

• Finally, you will get a Comma after 3 digits.

7. Add Comma Using Accounting Number Format

• Like before, select column E and right-click on it.
• Select the Format Cells.

• Now choose the Accounting category from the Number tab.
• Then, press OK.

• Finally, we see that in column E, we have added a comma using the Accounting category.

8. Using Shortcut Keys to Add Comma

• Select the E column first.
• Press Alt + H + K to add a Comma in the E column.

9. Using Comma Style to Put Comma in Numbers

• After selecting column E, go to the Home tab >> select Comma Style.
• It will put a comma in the E column. In this way, we can put commas in numbers.

10. Applying VBA to Add Comma at the End

• To add VBA code, you have to select Developer >> Visual Basic.

• Then select Insert and choose Module.

• Now we have to write the code below to add the comma at the end.

• You can copy the code from here.

Sub ConcatenateMobilePrice()
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet object to the active worksheet
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' Get the last row in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each row starting from row 4
    For i = 5 To lastRow
            ws.Cells(i, "D").Value = ws.Cells(i, "B").Value & "," & ws.Cells(i, "C").Value
    Next i
End Sub

Sub ConcatenateMobilePrice()

    Dim lastRow As Long

    Dim i As Long

    ' Set the worksheet object to the active worksheet

    Dim ws As Worksheet

    Set ws = ActiveSheet

' Get the last row in column B

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

  ' Loop through each row starting from row 4

    For i = 5 To lastRow

            ws.Cells(i, "D").Value = ws.Cells(i, "B").Value & "," & ws.Cells(i, "C").Value

    Next i

End Sub

• Finally, we get the D column with commas.

How to Add Commas in Excel Between Names

• To add commas between names, we have to select column C first and then choose Find & Select >> Replace.

• Now we have to replace space with commas. So, we select Replace >> put space in Find what >> Replace with a comma.
• Then pick Replace All.

• Then, we get the commas between names.

How to Add Commas in Excel Between Numbers

• To add commas between numbers, we have used the LEFT, MID, and RIGHT The formula is given below:

• Finally, we get commas in the D column.

Things to Remember

• Consider the number of decimal places you want to display. The comma formatting in Excel affects only the whole number part of a value and does not modify decimal places.
• If you want to display decimal places as well, you need to adjust the formatting accordingly.

Conclusion

In conclusion, adding commas to numbers in Excel is a simple and effective way to improve the readability and presentation of your data. By inserting commas into your numbers, you can quickly convey the magnitude of values in financial reports, data analysis, and various spreadsheet tasks, making your data more accessible and comprehensible to others.

Frequently Asked Questions

1. Can I apply comma formatting to a range of cells?

Yes, you can apply comma formatting to a range of cells in Excel. Simply select the range of cells you want to format and follow the steps mentioned earlier to add commas to the numbers.

2. Can I remove commas from numbers in Excel?

Yes, you can remove commas from numbers by changing the number formatting of the cell or range of cells. Select the cell or range, right-click, choose Format Cells, and then select the appropriate number formatting option without commas.

3. Does Excel support other regional comma separators?

Yes, Excel supports regional settings and automatically adjusts the comma separator based on the language and regional settings of your computer. For example, in some regions, a period may be used as a comma separator.

4. Is it possible to add commas to numbers using formulas in Excel?

Yes, you can use formulas to add commas to numbers in Excel. The TEXT function is commonly used for this purpose. For example, you can use the formula “=TEXT(C5,”#,##0.00″)” to add commas and display two decimal places for the value in cell C5.

How to Add Commas in Excel: Knowledge Hub

• Put Comma After 2 Digits
• Put Comma after 3 Digits
• Put Comma After 5 Digits
• Insert Comma Between Words 
• Add Comma Before Text 
• Insert Comma for Multiple Rows 
• Add a Comma Between City and State
• Use Comma in Excel Formula
• Add Thousand Separator
• Change Semicolon to Comma
• Excel Number Format Thousands with Comma
• Change Comma Style
• Change Comma to Indian style
• Convert Column to Row with Comma
• [Fixed!] Style Comma Not Found

<< Go Back to Concatenate Excel | Learn Excel