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:

`=B5&","&C5`

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

`=CONCATENATE(B5, ",",C5)`

### 3. Applying SUBSTITUTE Function to Replace Other Characters with Comma

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

`=SUBSTITUTE(D5,"-",",")`

### 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:

`=FIXED(D5*C5,0)`

### 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:

`TEXTJOIN(",",TRUE,B5,C5)`

**🔎 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
```

** ****🔎 Code Explanation**

```
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
```

In this part, we declare variables **lastRow**, **i**, and **ws** to store the last row number, loop counter, and the active worksheet object, respectively.

```
' Get the last row in column B
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
```

This line determines the last used row in column B. It starts from the last row of the worksheet **(ws.Rows.Count) **and moves upwards until it finds the last non-empty cell in **column B** **(ws.Cells(ws.Rows.Count, “B”).End(xlUp))**. The row number of the last non-empty cell in column B is then stored in the** lastRow** variable.

```
' 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
```

** **In the loop, we start from row** 5** and iterate through each row until we reach the last row **(lastRow)**. For each iteration, the code concatenates the values in column **B** and column **C** using the & operator, with a comma in between and stores the result in** column D (ws.Cells(i, “D”).Value).**

- 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:

`=LEFT(C5,3)&","&MID(C5,4,3)&","&RIGHT(C5,3)`

**🔎 Formula Explanation**

** **The formula **=LEFT(C5,3)&”,”&MID(C5,4,3)&”,”&RIGHT(C5,3)** manipulates the value in cell **C5** by extracting substrings from it and combining them with commas.

Let’s break down the formula:

**LEFT(C5,3**): The**LEFT function**extracts a specified number of characters from the left side of a text string. In this case, it takes the leftmost three characters from the text value in cell**C5**.**“&”,”&”**: The ampersand (&) is a concatenation operator in Excel that combines different text values. In this formula, it is used to concatenate the substrings and commas.**MID(C5,4,3)**: The**MID function**extracts a specified number of characters from a text string, starting from a given position. In this case, it takes three characters from cell**C5**, starting from the fourth position.**RIGHT(C5,3)**: The**RIGHT function**extracts a specified number of characters from the right side of a text string. In this case, it takes the rightmost three characters from the text value in cell**C5**.

- 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**