6 Easy Ways to Put Comma After 5 Digits in Excel

Every now and then we have to deal with a very large data entry. They may be the record of our social security card number, our driving license number record, or simply our bank account number. If the value is too large, it is hard to read them at one glance or understand them properly. So to make it easier for us to understand we may require to put a comma in some specific locations. In this article, we will show how to put comma after 5 digits in Excel. We will discuss 6 methods to do so. In the first four methods, we will imply the Excel functions such as  MID, LEFT, RIGHT, LEN, TEXT, etc. In our final two methods, we will take the advantage of an Excel feature called Format Cells & (VBA). You can find our intention from the overview picture given below.

📁 Download Excel File

Download the practice file from here.

Learn to Put Comma After 5 Digits in Excel with These 6 Methods

In this article, we will discuss 6 different methods to put a comma after 5 digits in Excel. Let’s assume that we have a company for which we want to buy some products. We have a dataset named Product Order Sheet including Product, Order, Unit Cost, and Total Cost. But the value is too large for us to understand. We want to add a comma after the Fifth digit to better represent the value and understand them quickly. In the next five methods, we will progressively learn about that. Our sample dataset is as follows.

This section will use the MID function to put a comma after 5 digits in Excel. This function gives us the flexibility to add any character at any location of a cell value. So if we want the choice of where we want to add a character and bring a change this function is preferable. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell F6 formula box and type this formula.

• Following that we use the Fill Handle tool to drag the formula.

• Finally, we get the result 15000,00 in the F6 cell in column F under Formatted Total Cost($).

📕 Read More: 9 Tricks to Put Comma After 2 Digits in Excel

In this particular section, we will apply the  MID, LEFT & LEN functions together to put a comma after 5 digits in Excel. This method requires three different functions whereas our first method only required one function. So it is not so user-friendly. But it too gives us the flexibility to decide where want to add a specific character. That‘s why it can be another methodology. So the steps for this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell F6 formula box and write the following formula.

• After that, we use the Fill Handle tool to drag the formula.
• Finally, we get the result in column F under Formatted Total Cost($).

📕 Read More: 10 Ways to Put a Comma After 3 Digits in Excel

In this method, we will learn how to apply the RIGHT, LEFT & LEN functions together to put a comma after 5 digits in Excel. This method is very much similar to the previous method. The only difference is that we use the RIGHT function instead of the MID function. It allows us to put a comma in any location of the cell data. Now the steps for this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell F6 formula box and write this formula.

• Following that we use the Fill Handle tool to drag the formula.
• Finally, we get the result in column F.

📕 Read More: 7 Easy Ways to Use Comma in Excel Formula

Here we will employ the TEXT function to put a comma after 5 digits. This function allows us to use any of the text formats we want to apply. So it is a very useful function for our arsenal. Also, it is easier to use. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to cell F6 and write this formula in the formula box.

• Here E6 denotes our text value.
• “00000””,””00″ means the text format we want to implement.

• Following that we use the Fill Handle tool to drag the formula.
• Finally, we get our result in column F.

📕 Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula

In this method, we will discuss how to use the Format Cells option to put a comma after 5 digits in Excel. This is not a function but rather an Excel feature. It gives us a dialogue box option from which we can customize any formatting according to our needs. Also, many default formatting is already available there. So it is a very useful tool. The steps for using this method are given below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select cell C6 and give a right-click.
• Following that we go to the Format Cells option.

• After that, we select the Number option and chose the Custom category.
• We write this in the Type box.

• Then we select the OK button.

• We apply the Format Cells option to the other cells as well individually.
• Finally, we get the result in column F.

In this section, we will discuss how to utilize the VBA Macro command to add a comma after 5 digits in Excel. Excel VBA is such a useful tool where we can preserve the commands by code. So it automatically gives us the result when we run the Macro command. If we have to deal with a very large dataset VBA becomes a very useful tool for us and saves a lot of time and energy. So the steps to utilize VBA are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Developer option.
• Then we select Visual Basic.

• After that, we select the Insert option.
• We select Module from there.

• Now we write this script in the module.

Code

Sub put_comma_after_5_digits()
Range("F6").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""00000"""",""""00"")"
Selection.AutoFill Destination:=Range("F6:F11")
End Sub

• We Save the code and Cancel the module.

• Now we go to the Macros option.
• After that, we Run the Macro.

• Finally, we get the result in column F.

How to Add Comma in Numbers in Excel

In this section, we will discuss how to add a comma in numbers in Excel using the FIXED function. This function simply rounds up a value and adds up a comma. But it doesn’t interfere with the number formats. So if we want to add a comma without changing the number type this function is the right option for it. So the steps for this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell F6 formula box and put this formula and hit the Enter button.

• Here E6 indicates our number and 0 indicates no decimal digits after rounding up.

• Following that we use the Fill Handle to drag the formula.
• Finally, we get our result in column F.

How to Add Comma Between Names in Excel

Here we will discuss how to add a comma in Excel between names by using the SUBSTITUTE function.

This function is capable of replacing one character with another character in a particular cell value. So if there is any mistake that we want to update this function is the right option for it. So the steps for using the method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell D6 formula box and write this formula and press Enter button.

• Here C6 is our text value.
• ” “,”,” denotes that we want to replace the blank space with a comma.

• After that, we apply the Fill Handle tool to drag the formula.
• Finally, we get the result in column D.

Apply Currency Format to Put Comma Before Last 3 Digit in Excel

Here in this section, we will discuss how to apply the currency feature to put a comma before the last 3 digits in Excel. This feature is very useful if our data contains any currency as a cell value. So it instantly adds a currency symbol before the data and also puts commas where necessary. But if our dataset is something other than currency then we should avoid it.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select our data range.
• Following that we select the Currency as the number format.

• We will find that our data has a Dollar Sign($) and Commas in it.
• After that, we again select the data range and apply the Decrease Decimal option.

• Finally, we get the result in column F.

📄  Important Notes

🖊️  We have to be careful about the function arguments when we use Excel functions.

🖊️  We have to apply the Format Cells option one by one to all data entries.

📝  Takeaways from This Article

📌  We can apply the MID function to put a comma after 5 digits in Excel.

📌  We can also use the MID, LEFT & LEN functions together to put a comma after 5 digits in Excel.

📌  Also using the  RIGHT, LEFT & LEN functions together is another way.

📌  If we want we can imply the TEXT function to put a comma after 5 digits in Excel.

📌 Apart from these methods, we can also apply the Format Cells option to put a comma after 5 digits in Excel.

📌  We can also use the Excel VBA  to put a comma after 5 digits.

Conclusion

From our discussion in this article, we have learned six different methods to put a comma after 5 digits in Excel. We can use different Excel functions like MID, LEFT, RIGHT, LEN, TEXT, etc for this purpose. We can also take the advantage of Excel features such as Format Cell & (VBA) to do the same job. As a user, you can use which one you feel most comfortable with. If any question comes to your mind after reading this article please feel free to put a comment n our comment box. We will try to answer them properly. If you are interested in further Excel-related problems and their solution process you can visit our website which is www.Excelden.com.

Related Articles

• 5 Easy Ways to Add Comma in Excel to Concatenate Cells
• 5 Tricks to Insert Comma Between Words in Excel