The use of commas is an essential part of numbers in excel. It is represented as the accounting position in a set of numbers that isolate thousands, hundreds, millions and all values considered. But sometimes we don’t get a spreadsheet where the numbers are defined in this way. So here is an essential article on how to put a comma after 3 digits in excel with 7 useful methods.
How to Put Comma after 3 Digits in Excel: 7 Methods
To illustrate, we have a dataset here. It shows 5 types of fruit quantity that a company sells throughout the year.
As you can see the values are not defined according to their accounting position, we will put a comma after 3 digits here to determine the thousand separators. Let’s see 7 useful methods to put a comma after 3 digits in Excel.
1. Apply Custom Format to Put Comma after 3 Digits
The application of Custom Format is a very quick one to put a comma after 3 digits. Follow the instructions below:
- First, right-click on cell C5.
- Then, select Format Cells from its Context Menu.
- After this, select Custom under the Number section in the new Format Cells dialogue box.
- Following, insert any of the symbols below inside the Type box and click OK:
###,###,###,###
00″,”000″,”000
#,##0.00
- Here, you can see the value in cell C5 now has a comma after 3 digits.
- Follow the same procedure for other cells.
- Otherwise, right-click on cell C5 and select Format Painter.
- Now, drag the brush icon over cell range C6:C9.
- The final output will look like this:
Read More: How to Apply Excel Number Format in Thousands with Comma
2. Put Comma after 3 Digits with Excel MID Function
This method will show you how to put a comma after 3 digits with the MID function.
- First, select cell C5.
- After that, insert the formula into the cell.
=MID(C5,1,1)&","&MID(C5,2,2)&","&MID(C5,4,3)
Here, the MID function extracts the comma for each value in the dataset defining its location in the text string.
- Now, press Enter.
- You can see the value has a comma in between.
- Apply the same process in the cell range C6:C9.
Read More: How to Use Comma in Excel Formula
3. Use IF Function for Putting Comma after 3 Digits in Excel
In this method, we will use the IF function in excel. The IF function helps to make a comparison between a value and its output after applying the formula. Let’s see how it works:
- First, select cell C5.
- Here, insert the formula below:
=IF(LEN(C5)=6,REPLACE(C5,2,5,","&MID(C5,2,2))&","&MID(C5,4,3))
- Then, press Enter.
- The result will look like this:
🔎 How Does the Formula Work?
- IF(LEN(C5)=6: Checks the condition with the LEN function to count the number of characters in the text string. The value is 6 here.
- REPLACE(C5,2,5): The REPLACE function replaces the numbers in a certain location given text string with another text string.
- “&MID(C5,2,2))&”: it extracts the comma for each interval in the value.
- Following the similar process, you can put a comma after 3 digits in cell range C6:C9.
Read More: How to Insert Comma in Excel for Multiple Rows
4. Insert TEXT Function to Insert Comma in Excel
We can also use the TEXT function to insert commas in excel. It will help to determine the location of each comma in a text string. Follow the steps below:
- First, insert the formula in cell C5.
=TEXT(C5,"00"",""000")
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.
- Now, press Enter.
- The result will show up like this:
- Finally, apply the similar process in cell range C6:C9.
Similar Readings
- How to Put Comma in Numbers in Excel
- How to Put Comma After 2 Digits in Excel
- How to Put Comma After 5 Digits in Excel
- How to Convert Column to Row with Comma in Excel
5. Set Comma after 3 Digits with Excel LEFT, MID & RIGHT Functions
This is a very interesting method to set a comma after 3 digits. Here we will combine the LEFT, MID and RIGHT functions to determine the position of the numeric values along with the thousand separators. Let’s see the process below:
- In the beginning, insert the formula in cell C5.
=LEFT(C5,3)&","&RIGHT(C5,3)
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. In case of more than 6 digits, we will add the MID function to determine the intervals.
- After that, press Enter.
- Finally, you can see there is a comma after 3 digits.
- Follow the same procedure and you will get the output in cell range C6:C9 like this:
6. Put Comma after 3 Digits with Comma Style
Another useful method to put a comma is to use the Comma Style. Let’s the simple steps below:
- First select cell range C5:C9.
- Next, go to the Home tab.
- Here, click on the Comma Style from the Number group.
- Right after that, it will show a comma after 3 digits in each cell value.
- To increase or decrease the decimal point, click on the icons shown in this image:
- Finally, we have our required output.
7. Use Excel VBA to Put Comma after 3 Digits
In this last method, we will use Excel VBA Macro code to put a comma after 3 digits. Let’s follow the process:
- In the beginning, select Visual Basic under the Code group in the Developer tab.
- Following, select Module from the Insert section in the new window.
- Now, insert the 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
- Then, click on the Rub Sub button or press F5 on your keyboard.
- After that, click on Run in the Macros window.
- Finally, we have successfully inserted commas in all the values.
- You can change the 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. So make sure before using any of the methods above.
- In the case of Custom Format, it will only change the selected cell with a comma, not the entire worksheet.
Download Practice Workbook
Download the sample file and try it yourself.
Conclusion
Hope this was a helpful article on how to put a comma after 3 digits in Excel. We tried to show you 7 useful methods for this. Let us know your suggestions.
Related Articles
- How to Add Comma in Excel Between Names
- How to Add Comma in Excel at the End
- How to Insert Comma Between Words in Excel
- How to Add Comma Before Text in Excel
- How to Add a Comma Between City and State in Excel
- How to Change Comma Style in Excel
- 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 Remove Commas in Excel
