10 Ways to Put a Comma After 3 Digits in Excel

For better representation of data, we often need to use commas to break sentences and numbers. Numbers are usually separated by commas to represent the value of large numbers easily in thousands, millions, or greater numbers. In this article, we will look at different approaches to put a comma after every 3 digits in Excel starting from the rightmost part of the number to the left. To begin with, here is a brief overview of the approaches we will use to put a comma after 3 digits in Excel.

📁 Download Excel File

Download the practice workbook below.

Learn to Put a Comma After 3 Digits in Excel with These 10 Approaches

To demonstrate things easily, we will consider a dataset where we have different account holder names with their savings amount. We will use the numerical data in the Savings column to put commas after 3-digit intervals. The dataset is represented below.

Comma Style is one type of Number Format that allows us to put a comma after 3 digits in Excel. To navigate to this option so that we can put commas after 3 digits in Excel, we need to follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select the desired cell range that we want to change the format. In this case, we select cell range C6:C13.
• Next, we go to the Home tab and select the Comma Style option from the Number segment of the Ribbon.

• The selected cells will show a comma after every three digits.
• Furthermore, to remove the decimal points, we select the Decrease Decimal option of the Number segment twice.

• Thus, the savings data will come out as output with commas after three digits. This is shown in the following image.

📕 Read More: 9 Tricks to Put Comma After 2 Digits in Excel

Formatting cell data in the currency dollar format can automatically separate our data entries with commas after three-digit intervals in Excel. In this approach, we will do a currency formatting of cells in dollars to put commas after every 3 digits in Excel. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, we select the cell range C6:C13.
• Then, we right-click and select the Format Cells option.

• After that, the Format Cells window will appear. In the Category section, we select Currency and select the value of the Decimal places to be 0 (zero).
• Then, we press OK.

• We observe that, in the Savings column, our data will be visible in the dollar currency format with commas separating the number after every 3 digits.

📕 Read More: 6 Easy Ways to Put Comma After 5 Digits in Excel

Formatting cell data, especially numbers can be another approach by which we can put commas after certain digits in Excel. In this approach, we will do a custom formatting of cells that will allow us to separate large digits with the help of commas. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we select the cell range C6:C13.
• Then, we right-click and select the Format Cells option.

• The Format Cells window will appear.
• We select the Custom number format under the Number segment.
• We insert any one of the following on the Type box and then press OK.

• We will see that the cells that have been selected will now have commas separating the number after every three digits starting from the right. The output image is shown below.

📕 Read More: 7 Easy Ways to Use Comma in Excel Formula

The DOLLAR function converts the argument into a currency format similar to the second approach. However, we are using a function here, not formatting options. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6 and write down the following formula in the formula bar.

• Next, we press Enter.
• We will see that the commas will be present at every 3-digit interval starting from the right side of the number.
• Now, we drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• The output results will be visible like the following image in currencies.

The FIXED function converts the number into text with the decimal format, periods, and commas. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6 and write down the following formula in the formula bar.

• Next, we press Enter.
• We will see that the commas will be present at every 3-digit interval starting from the right side of the number.
• Now, we drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• The output results will be visible like the following image in currencies.

📕 Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula

The TEXT function along with its arguments can help us put commas after a certain number of digits in Excel. We can specify the placement location of the comma by indicating the exact number of zeros as arguments in the TEXT function. To understand it clearly, we can break it down into the following steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell C6 and write down the following formula in the formula bar.

• After that, we press Enter.
• We will see that the commas will be placed at the exact positions as determined by the number of zeros in the TEXT function formula. So, the zeros will be placed at an interval of 3 digits starting from the right.
• Now, we drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• The results of savings amounts will be separated by commas after every 3 digits as in the following image.

We can combine IF, LEN, REPLACE, and MID functions together to put commas after 3 digits in Excel. Using the LEN function to determine the string length, we can put a nested IF logic to find the places where we can put commas. The steps to do this are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we select cell D6 and write down the following formula in the formula bar.

• Then, we drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• Thus, we will see that the numbers are separated by commas every three digits starting from the right. Here is the output image.

In this section, we worked with 8 and 9 digits. If we have other numbers of digits in the dataset, need to modify this formula.

The MID function finds a certain number of characters from a text string. We can use this function to extract a certain portion of a string, place a comma after that portion and then place another such portion after that comma. In this way, we can place commas after 3 digits in Excel. For the steps involving with MID function, we will use a dataset that has the same length of digits. Otherwise, for digits of different lengths, the formula arguments will change. The dataset is as follows:

The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• We select cell C6 and write down the following formula in the formula bar.

• Then, we press Enter.
• After that, we drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• We will see that the whole number will be separated by commas every three intervals starting from the right of the number to the left.

The number of digits present is an important factor that we have to take into account in case of using this formula. Based on the number of digits, the second and third arguments of the function will change. For example, in the above-mentioned formula, the MID function picks the string from cell C6, selects the first character as indicated by the second argument, and selects a sub-string group by taking 2 characters as mentioned by the third argument 2. Here we have selected 2 as the third argument as our dataset has 8 characters. Contrarily, if we have a 9-digit number, we have to separate them into groups of 3. In that case, our third argument for the MID function will be 3.

📕 Read More: 5 Easy Ways to Add Comma in Excel to Concatenate Cells

A combination of LEFT, MID, and RIGHT functions can enable us to put commas after 3 digits in numbers. The use of these functions is to collect certain portions of strings and break them into some sub-strings. The number of characters that we can extract using these functions is present in their arguments. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell D6.
• Then we write the following formula in the formula bar and press Enter.

• We drag the Fill Handle at the bottom right of cell D6 up to cell D13.

• We will see the comma-separated values as results similar to the following image.

VBA Code is one of the most convenient approaches for solving and handling a large volume of data. To write a VBA code, we need to follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select the cell range D6:D13.
• After that, we right-click on our Excel sheet named VBA Code and select View Code.

• The Visual Basic Editor will open. We select Insert and create a new Module.

• In the newly created Module, we write the following VBA Code.

Sub Commaafterthreedigits()
Dim Cell_Range As Range
Set Cell_Range = Selection
For Each cell In Cell_Range
If IsNumeric(cell.Value) Then
Dim strN As String
strN = CStr(cell.Value)
Dim Digit_Number As String
Digit_Number = Format(cell.Value, "#,###")
cell.Value = Digit_Number
End If
Next cell
End Sub

• Then, we press the Run button.

• As a result, we will see that our selected range of cells will now have commas after 3 digits. It is shown in the following image.

How to Put a Comma After 2 Digits in Excel

We have looked at various approaches by which we can put a comma after two digits in Excel. We can put separators like commas after any digit we like by specifying it in the formulas. In this approach, we will have a look at one of the ways in which we can put a comma after 2 digits in Excel. We will use the MID function in this example. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, we select cell D6.
• Then, we write the following formula in the formula bar.

• Now, we drag the Fill Handle from the bottom right end of cell D6 up to cell D13.

• Thus, we will observe that the numbers have been separated by only a comma after two digits like in the image below.

📄  Important Notes

🖊️  Some countries use a comma as a decimal separator instead of a full stop. So, we should keep this in mind while working with such data.

🖊️  Certain functions like MID, LEFT, and RIGHT have arguments that can vary depending on the number of digits or characters that our number has. So, we should keep this in mind when we are working with different numbers.

📝  Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can put commas after 3 digits intervals in numbers in Excel using basic Format Cells options like Comma Style, Currency, and Custom Formats.

📌  We can use different functions such as the DOLLAR function, FIXED function, and TEXT function to put commas after 3 digits in Excel.

📌  Additionally, we can also use a combination of different functions like IF, LEN, REPLACE, MID, LEFT, and RIGHT to put commas after 3 digits in Excel.

📌  Alternatively, we can also write a VBA code to put commas after 3 digits in Excel which is beneficial for a large volume of data.

Conclusion

To conclude, I hope that this article has helped you to understand how to put a comma after every 3 digits in Excel. If you have any queries, reach out to us by leaving a comment below. Follow our website ExcelDen for more informative articles related to Excel.

Related Article

• 5 Tricks to Insert Comma Between Words in Excel