How to Put a Comma after 3 Digits in Excel (7 Useful Methods)

Below is a dataset showing 5 types of fruits a company sells throughout the year.

Method 1 – Applying Custom Format 

Steps:

• Right-click on cell C5.
• Select Format Cells from the Context Menu.

• Select Custom under Number in the new Format Cells dialogue box.

• Enter any of the symbols below in the Type box and click OK:

• Here, the value in cell C5 now has a comma after 3 digits.

• Follow the same procedure for the other cells.

OR

• Right-click on cell C5 and select Format Painter.

• Drag the brush icon over cell range C6:C9. The final output will look like this:

Read More: How to Put Comma in Numbers in Excel

Method 2 – Using the Excel MID Function

Steps:

• Select cell C5.
• Enter the following formula:

Here, the MID function extracts the comma for each value in the dataset, defining its location in the text string.

• Press Enter. The result will look like this:

• Apply the same process to the cell range C6:C9.

Read More: How to Put Comma After 2 Digits in Excel

Method 3 – Using the IF Function

Steps:

• Select cell C5.
• Enter the following formula:

• Press Enter. The result will look like this:

Formula Breakdown

• Following the same process, you can put a comma after 3 digits in cell range C6:C9.

Read More: How to Put Comma After 5 Digits in Excel

Method 4 – Inserting TEXT Function 

Steps:

• Select cell C5.
• Enter the following formula:

Here, the intervals with 0 determine the location of each comma in the value. It will differ according to the number of characters in the text string.

• Press Enter. The result will look like this:

• Apply the same process in cell range C6:C9.

Method 5 – Using Excel LEFT, MID & RIGHT Functions

Steps:

• Select cell C5.
• Enter the following formula:

Here, the value has 6 digits. Therefore, we only used the LEFT and RIGHT functions to put a comma after every 3 digits from left to right. If the value has more than 6 digits, we will add the MID function to determine the intervals.

• Press Enter. The results will look like this:

• Apply the same procedure to cell range C6:C9.

Method 6 – Using the Comma Style

Steps:

• Select cell range C5:C9.

• Go to the Home tab.
• Click on the Comma Style from the Number group.

• There will now be a comma after 3 digits in each cell value.

• To increase or decrease the decimal point, click on the icons shown below:

• The results will look like this:

Read More: How to Change Comma Style in Excel

Method 7 – Using Excel VBA 

Steps:

• Select Visual Basic under the Code group in the Developer tab.

• Select Module from the Insert section in the new window.

• Enter the following code on the blank page:

Sub FormatNumberWithComma()
Range("D5") = Format(Range("C5"), "#,###")
Range("D6") = Format(Range("C6"), "#,###.00")
Range("D7") = Format(Range("C7"), "#,###")
Range("D8") = Format(Range("C8"), "#,##0.00")
Range("D9") = Format(Range("C9"), "#,###")
End Sub

• Click on the Rub Sub button or press F5 on your keyboard.

• Click on Run in the Macros window.

• We have successfully inserted commas in all the values.

• You can change or remove the decimal points by clicking the Decrease Decimal icon in the Home tab.

Things to Remember

• While using any of the functions described above, make sure you put the positions of the intervals properly to get your desired result.
• Thousand Separator might be a Comma (,) or a Full Stop (.) according to country region.
• In the case of Custom Format, the selected cell will only be changed with a comma, not the entire worksheet.

Download the Practice Workbook

Download the workbook to practice.

Related Articles

• How to Change Semicolon to Comma in Excel
• How to Change Comma in Excel to Indian Style
• [Fixed!] Style Comma Not Found in Excel
• How to Convert Column to Row with Comma in Excel

<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel