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.

Overview image to put a comma after 5 digits in excel


πŸ“ 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.

Sample Dataset of Product Order Sheet

method

1. Applying MID Function to Put Comma After 5 Digits

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.

=MID(E6,1,5)&”,”&MID(E6,6,5)

πŸ”¨ Formula Breakdown

πŸ‘‰Β  Here MID(E6,1,5) returns the first five digits from cell E6.

πŸ‘‰Β  MID(E6,6,5) returns the remaining characters after the sixth digit from cell E6.

πŸ‘‰Β  We use two Ampersands &”,”& to add a comma in between those 2 function returns.

Using the MID function to put a comma after 5 digits

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

Using the Fill handle tool

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

comma applied after 5 digits

πŸ“• Read More: 9 Tricks to Put Comma After 2 Digits in Excel

method

2. Applying LEFT, MID & LEN Functions Combined to Put Comma

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.

=LEFT(E6,5)&”,”&MID(E6,6,LEN(E6)-5)

πŸ”¨ Formula Breakdown

πŸ‘‰Β  Here LEFT(E6,5) gives us the first five digits of cell value E6.

πŸ‘‰Β  LEN(E6)-5 returns the character length of cell E6 after deducting by 5 strings.

πŸ‘‰Β  We get the remaining characters after the sixth digit of cell E6 by MID(E6,6,LEN(E6)-5).

πŸ‘‰Β  &”,”& adds a comma in between the two function returns.

Using the LEFT, MID & LEN functions together

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

Comma after 5 digits

πŸ“• Read More: 10 Ways to Put a Comma After 3 Digits in Excel

method

3. Using LEFT, RIGHT & LEN Functions to Put Comma After 5 Digits

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.

=LEFT(E6,5)&”,”&RIGHT(E6,LEN(E6)-5)

πŸ”¨ Formula Breakdown

πŸ‘‰Β  LEFT(E6,5) returns us the first five digits of cell value E6 from left.

πŸ‘‰Β  LEN(E6)-5 gives us the character length of cell E6 after deducting 5.

πŸ‘‰Β  We get the remaining characters after the sixth digit from the right of cell E6 by RIGHT(E6,LEN(E6)-5).

πŸ‘‰Β  &”,”& adds a comma in between the two function returns.

Using the LEFT, RIGHT & LEN functions together

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

Cell value with a comma

πŸ“• Read More: 7 Easy Ways to Use Comma in Excel Formula

method

4. Implying TEXT Function to Put Comma

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.

=TEXT(E6,”00000″”,””00β€³)

  • Here E6 denotes our text value.
  • β€œ00000””,””00β€³ means the text format we want to implement.

Using the TEXT function

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

Cell value with a comma after 5 digits

πŸ“• Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula

method

5. Using Format Cells Option to Put Comma After 5 Digits

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.

Using the Format cells option.

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

0,”0,””00β€³

  • Then we select the OK button.

Choosing custom number type

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

cells with a comma after 5 digits

method

6. Utilize Excel VBA Macro to Add Comma After 5 Digits

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.

Selecting the Visual Basic

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

Inserting the module

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

VBA code to add comma

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

Running the macro

  • Finally, we get the result in column F.

Cell value with a comma


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.

=FIXED(E6,0)

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

Using the FIXED function

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

cell value with comma


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.

=SUBSTITUTE(C6,” β€œ,”,”)

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

Using the SUBSTITUTE function

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

Comma between names


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.

Selecting the currency 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.

Decreasing the decimal value

  • Finally, we get the result in column F.

the comma after 3 digits


πŸ“„Β  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

(Visited 32 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo