How to Put Comma after 3 Digits in Excel (7 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

The use of commas is an essential part of numbers in excel. It is represented as the accounting position in a set of numbers that isolate thousands, hundreds, millions and all values considered. But sometimes we don’t get a spreadsheet where the numbers are defined in this way. So here is an essential article on how to put a comma after 3 digits in excel with 7 useful methods.


How to Put Comma after 3 Digits in Excel: 7 Methods

To illustrate, we have a dataset here. It shows 5 types of fruit quantity that a company sells throughout the year.

How to Put Comma after 3 Digits in Excel

As you can see the values are not defined according to their accounting position, we will put a comma after 3 digits here to determine the thousand separators. Let’s see 7 useful methods to put a comma after 3 digits in Excel.

1. Apply Custom Format to Put Comma after 3 Digits

The application of Custom Format is a very quick one to put a comma after 3 digits. Follow the instructions below:

  • First, right-click on cell C5.
  • Then, select Format Cells from its Context Menu.

Apply Custom Format to Put Comma after 3 Digits

  • After this, select Custom under the Number section in the new Format Cells dialogue box.

Apply Custom Format to Put Comma after 3 Digits

  • Following, insert any of the symbols below inside the Type box and click OK:

###,###,###,###

00″,”000″,”000

#,##0.00

Apply Custom Format to Put Comma after 3 Digits

  • Here, you can see the value in cell C5 now has a comma after 3 digits.

  • Follow the same procedure for other cells.
  • Otherwise, right-click on cell C5 and select Format Painter.

  • Now, drag the brush icon over cell range C6:C9.
  • The final output will look like this:

How to Put Comma after 3 Digits in Excel

Read More: How to Put Comma in Numbers in Excel


2. Put Comma after 3 Digits with Excel MID Function

This method will show you how to put a comma after 3 digits with the MID function.

  • First, select cell C5.
  • After that, insert the formula into the cell.
=MID(C5,1,1)&","&MID(C5,2,2)&","&MID(C5,4,3)

Put Comma after 3 Digits with Excel MID Function

Here, the MID function extracts the comma for each value in the dataset defining its location in the text string.

  • Now, press Enter.
  • You can see the value has a comma in between.

Put Comma after 3 Digits with Excel MID Function

  • Apply the same process in the cell range C6:C9.

Put Comma after 3 Digits with Excel MID Function

Read More: How to Put Comma After 2 Digits in Excel


3. Use IF Function for Putting Comma after 3 Digits in Excel

In this method, we will use the IF function in excel. The IF function helps to make a comparison between a value and its output after applying the formula. Let’s see how it works:

  • First, select cell C5.
  • Here, insert the formula below:
=IF(LEN(C5)=6,REPLACE(C5,2,5,","&MID(C5,2,2))&","&MID(C5,4,3))

Use IF Function for Putting Comma after 3 Digits in Excel

  • Then, press Enter.
  • The result will look like this:

🔎 How Does the Formula Work?

  • IF(LEN(C5)=6: Checks the condition with the LEN function to count the number of characters in the text string. The value is 6 here.
  • REPLACE(C5,2,5): The REPLACE function replaces the numbers in a certain location given text string with another text string.
  • “&MID(C5,2,2))&”: it extracts the comma for each interval in the value.
  • Following the similar process, you can put a comma after 3 digits in cell range C6:C9.

Use IF Function for Putting Comma after 3 Digits in Excel

Note: The values in the LEN and MID functions will vary according to the number of characters.

Read More: How to Put Comma After 5 Digits in Excel


4. Insert TEXT Function to Insert Comma in Excel

We can also use the TEXT function to insert commas in excel. It will help to determine the location of each comma in a text string. Follow the steps below:

  • First, insert the formula in cell C5.
=TEXT(C5,"00"",""000")

Insert TEXT Function to Insert Comma in Excel

Here, the intervals with 0 determine the location of each comma in the value. It will differ according to the number of characters in the text string.

  • Now, press Enter.
  • The result will show up like this:

  • Finally, apply the similar process in cell range C6:C9.

Insert TEXT Function to Insert Comma in Excel


5. Set Comma after 3 Digits with Excel LEFT, MID & RIGHT Functions

This is a very interesting method to set a comma after 3 digits. Here we will combine the LEFT, MID and RIGHT functions to determine the position of the numeric values along with the thousand separators. Let’s see the process below:

  • In the beginning, insert the formula in cell C5.
=LEFT(C5,3)&","&RIGHT(C5,3)

Set Comma after 3 Digits with Excel LEFT, MID & RIGHT Functions

Here, the value has 6 digits. Therefore, we only used the LEFT and RIGHT functions to put a comma after every 3 digits from left to right. In case of more than 6 digits, we will add the MID function to determine the intervals.

  • After that, press Enter.
  • Finally, you can see there is a comma after 3 digits.

  • Follow the same procedure and you will get the output in cell range C6:C9 like this:

Set Comma after 3 Digits with Excel LEFT, MID & RIGHT Functions


6. Put Comma after 3 Digits with Comma Style

Another useful method to put a comma is to use the Comma Style. Let’s the simple steps below:

  • First select cell range C5:C9.

Put Comma after 3 Digits with Comma Style 

  • Next, go to the Home tab.
  • Here, click on the Comma Style from the Number group.

Put Comma after 3 Digits with Comma Style 

  • Right after that, it will show a comma after 3 digits in each cell value.

Put Comma after 3 Digits with Comma Style 

  • To increase or decrease the decimal point, click on the icons shown in this image:

  • Finally, we have our required output.

Read More: How to Change Comma Style in Excel


7. Use Excel VBA to Put Comma after 3 Digits

In this last method, we will use Excel VBA Macro code to put a comma after 3 digits. Let’s follow the process:

  • In the beginning, select Visual Basic under the Code group in the Developer tab.

Use Excel VBA to Put Comma after 3 Digits

  • Following, select Module from the Insert section in the new window.

Use Excel VBA to Put Comma after 3 Digits

  • Now, insert the code on the blank page.
Sub FormatNumberWithComma()
Range("D5") = Format(Range("C5"), "#,###")
Range("D6") = Format(Range("C6"), "#,###.00")
Range("D7") = Format(Range("C7"), "#,###")
Range("D8") = Format(Range("C8"), "#,##0.00")
Range("D9") = Format(Range("C9"), "#,###")
End Sub

Use Excel VBA to Put Comma after 3 Digits

  • Then, click on the Rub Sub button or press F5 on your keyboard.

  • After that, click on Run in the Macros window.

Use Excel VBA to Put Comma after 3 Digits

  • Finally, we have successfully inserted commas in all the values.

Use Excel VBA to Put Comma after 3 Digits

  • You can change the remove the decimal points by clicking the Decrease Decimal icon in the Home tab.


Things to Remember

  • While using any of the functions described above, make sure you put the positions of the intervals properly to get your desired result.
  • Thousand Separator might be a Comma (,) or a Full Stop (.) according to country region. So make sure before using any of the methods above.
  • In the case of Custom Format, it will only change the selected cell with a comma, not the entire worksheet.

Download Practice Workbook

Download the sample file and try it yourself.


Conclusion

Hope this was a helpful article on how to put a comma after 3 digits in Excel. We tried to show you 7 useful methods for this. Let us know your suggestions.


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.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo