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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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


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


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

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


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


<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo