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.

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

- 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**

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

- 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**

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

- 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**

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

- 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**

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

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

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

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

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

**=FIXED(E6,0)**

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

**=SUBSTITUTE(C6,” “,”,”)**

- 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**