Commas are used to represent numbers properly. There is some format of using commas with a number. Usually, commas are used after 3 digits in a number. But sometimes we need to use commas with customizing positions. Like, here we want to put a comma after 5 digits in Excel. In this article, we discuss how to put commas after 5 digits in Excel.
How to Put Comma After 5 Digits in Excel: 5 Methods
In this article, we will discuss 6 methods including VBA to show how to put a comma after 5 digits in Excel. Have a look at the below methods. We have a dataset of some sales persons with their yearly sales.
1. Use MID Function to Put Comma After 5 Digits
In this section, we will use only the MID function with the ampersand(&) to put a comma after 5 digits in Excel.
📌 Steps:
- Add a new column to the dataset.
- Now, go to Cell D5.
- Put the following formula on that cell.
=MID(C5,1,5)&","&MID(C5,6,5)
- Press the Enter button.
We get the result.
- Now, drag the Fill Handle icon downwards.
Read More: How to Put Comma in Numbers in Excel
2. Use Combination of LEFT, MID, and LEN Functions
In this section, we will use a combination of the LEFT, MID, and LEN functions to put a comma after 5 digits in Excel.
📌 Steps:
- Now, put the formula based on the combination of LEFT, MID, and LEN functions on Cell D5.
=LEFT(C5,5)&","&MID(C5,6,LEN(C5)-5)
- Again, press the Enter button and drag the Fill Handle icon.
We can see commas are inserted after 5 digits in the following numbers.
Formula Explanation:
- LEN(C5)
It finds the length of Cell C5.
Result: 9
- LEN(C5)-5
Subtracts 5 from the length of C5.
Result: 4
- MID(C5,6,LEN(C5)-5)
Presents the digits from the 6th position to the last.
Result: 5123
- LEFT(C5,5)
Showes 5 digits from the left side.
Result: 13564
- LEFT(C5,5)&”,”&MID(C5,6,LEN(C5)-5)
Connects two parts with a comma in between.
Result: 13564,5123
3. Combine LEFT, RIGHT, and LEN Functions
We use the combination of the LEFT, RIGHT, and LEN functions to add a comma after 5 digits.
📌 Steps:
- Now, go to Cell D5 and put the following formula on that cell.
=LEFT(C5,5)&","&RIGHT(C5,LEN(C5)-5)
- Then, click the Enter button.
- After that, pull the Fill Handle icon downwards.
Formula Explanation:
- LEN(C5)
It finds the length of Cell C5.
Result: 9
- LEN(C5)-5
Subtracts 5 from the length of C5.
Result: 4
- RIGHT(C5,LEN(C5)-5)
Shows digits from the right side.
Result: 5123
- LEFT(C5,5)
Showes 5 digits from the left side.
Result: 13564
- LEFT(C5,5)&”,”&RIGHT(C5,LEN(C5)-5)
Connects two parts with a comma in between.
Result: 13564,5123
4. Apply Excel TEXT Function
In this section, we will apply the TEXT function to put a comma after 5 digits. We will put a format in the formula that will indicate in which position the comma will be added.
📌 Steps:
- Copy and paste the following formula on Cell D5.
=TEXT(C5,"00000"",""00000")
- Then, press the Enter and drag the Fill Handle icon.
We can see a comma added after 5 digits. One thing that needs to mention is that this formula will add commas counting digits from the right side. And if there are not 5 digits available, it will fill up the 5 digits with Zeros(0).
5. Put Comma After 5 Digits from Format Cells Feature
Here, we will use the Format Cells feature to add a comma after 5 digits.
📌 Steps:
- First, we copy the data of Sales ($) by pressing Ctrl+C. And paste it into the next column by pressing Ctrl+V.
- Now, select Range D5:D9.
- Press the right button of the mouse.
- Choose the Format Cells option from the Context Menu.
- Format Cells window appears.
- Choose the Custom option from the Number tab.
- Put a Custom format in the type box.
- Finally, press the OK button.
We can see a comma added to the result. But there is a difference. That is an extra Zero(0) added at the left side of the number.
Read More: How to Put Comma After 2 Digits in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 5 methods to explain how to put a comma after 5 digits in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.
Related Articles
- How to Convert Column to Row with Comma 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 Put Comma after 3 Digits in Excel
<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel