How to Put Comma After 5 Digits in Excel (5 Methods)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Methods to Put Comma After 5 Digits in Excel

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.

Sample data to put comma after 5 digits in Excel


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 row to the dataset.

  • Now, go to Cell D5.
  • Put the following formula on that cell.
=MID(C5,1,5)&","&MID(C5,6,5)

Formula of MID Function to Put Comma After 5 Digits

  • Press the Enter button.

We get the result.

  • Now, drag the Fill Handle icon downwards.

Pull the fill handle icon to expand the effect of the MID function

Read More: How to Put Comma After 2 Digits in Excel (9 Quick Methods)


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)

Formula to Put Comma after 5 digits Using the Combination of LEFT, MID, and LEN Functions

  • 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

Read More: How to Use Comma in Excel Formula (2 Suitable Ways)


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)

Formula to Add Comma After 5 Digits with the Combination of LEFT, RIGHT, and LEN Functions

  • 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

Read More: How to Add Thousand Separator in Excel Formula


4. Apply Excel TEXT Function

In this section, we will apply the Excel 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 add.

📌 Steps:

  • Copy and paste the following formula on Cell D5.
=TEXT(C5,"00000"",""00000")

Formula of Excel TEXT Function to Put Comma

  • Then, press the Enter and drag the Fill Handle icon.

Result after applying TEXT function to put comma in Excel

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

Read More: How to Add Comma in Excel to Concatenate Cells (5 Easy Ways)


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.

Copy and paste data data

  • Now, select Range D5:D9.
  • Press the right button of the mouse.
  • Choose the Format Cells option from the Context Menu.

Introduce formula cell in Excel

  • Format Cells window appears.
  • Choose the Custom option from the Number tab.
  • Put a Custom format in the type box.

Change the format of cells to put comma after 5 digits in excel

  • 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: Insert Comma in Excel for Multiple Rows (3 Suitable Ways)


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 have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo