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.

Overview of putting commas 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.

Sample dataset to put commas after 3 digit in Excel

Approach

1. Put Comma Using Comma Style of Number Format

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.

Choosing Comma Style option

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

Removing decimal points

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

Putting a comma after 3 digits using the Comma Style option

๐Ÿ“• Read More: 9 Tricks to Put Comma After 2 Digits in Excel

Approach

2. Apply Currency Format to Set Comma

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.

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

Formatting cells as currency in Format Cells window

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

Putting a comma after 3 digits using Currency format.

๐Ÿ“• Read More: 6 Easy Ways to Put Comma After 5 Digits in Excel

Approach

3. Incorporating Custom Format

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.

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

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

00","000","000

#,##0.00

Choosing Custom Format.

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

Putting a comma after 3 digits using Custom Format.

๐Ÿ“• Read More: 7 Easy Ways to Use Comma in Excel Formula

Approach

4. Use DOLLAR Function to Add Comma

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.
=DOLLAR(C6,0)

๐Ÿ”จ Formula Breakdown

DOLLAR(C6,0)

๐Ÿ‘‰ย  The first argument C6 denotes the cell whose format will be converted into a currency format.

๐Ÿ‘‰ย  The second argument 0 (Zero) indicates there will be no decimal values.

DOLLAR Function

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

Dragging Fill Handle

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

Putting a comma after 3 digits using DOLLAR Function.

Approach

5. Set Comma Using Fixed Function

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.
=FIXED(C6,0)

๐Ÿ”จ Formula Breakdown

FIXED(C6,0)

๐Ÿ‘‰ย  The first argument C6 denotes the cell whose format will be converted into text format with comma separators by default.

๐Ÿ‘‰ย  The second argument 0 (Zero) indicates there will be no decimal values.

FIXED Function

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

Dragging Fill Handle

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

Putting a comma after 3 digits using FIXED Function

๐Ÿ“• Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula

Approach

6. Inserting TEXT Function

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.
=TEXT(C6,"00"",""000"",""000")

๐Ÿ”จ Formula Breakdown

TEXT(C6,โ€00โ€ณโ€,โ€โ€000โ€ณโ€,โ€โ€000โ€ณ)

๐Ÿ‘‰ย  The first argument C6 specifies the cell value we will change the format.

๐Ÿ‘‰ย  โ€œ00โ€โ€,โ€โ€000โ€ณโ€,โ€โ€000โ€ณ denotes the position or place values in the numbers after which we will use a separator like a comma.

Inserting TEXT Function

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

Dragging Fill Handle

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

Putting a comma after 3 digits using TEXT Function

Approach

7. Incorporating IF, LEN, REPLACE, and MID Functions

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.

=IF(LEN(C6)=8,REPLACE(C6,3,6,","&MID(C6,3,3))&","&MID(C6,6,3),IF(LEN(C6)=9,REPLACE(C6,4,6,","&MID(C6,4,3))&","&MID(C6,7,3)))

๐Ÿ”จ Formula Breakdown

IF(LEN(C6)=8,REPLACE(C6,3,6,โ€,โ€&MID(C6,3,3))&โ€,โ€&MID(C6,6,3),IF(LEN(C6)=9,REPLACE(C6,4,6,โ€,โ€&MID(C6,4,3))&โ€,โ€&MID(C6,7,3)))

๐Ÿ‘‰ย  MID(C6,3,3) function looks for the text string in C6, and selects a group of 3 digits or characters starting from the third character in the string.

๐Ÿ‘‰ย  REPLACE(C6,3,6,โ€,โ€&MID(C6,3,3)) suggest that from the data in cell C6, 3 represents the position of the character from where we want to replace with new text. 6 is the number of characters we are replacing from the third character.ย  โ€œ,โ€&MID(C6,3,3) is the newly replaced text.

๐Ÿ‘‰ย  The IF function gives an output based on the entry length determined by the LEN function.

Inserting Combined Function

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

Dragging Fill Handle

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

Putting a comma after 3 digits using Combined Functions

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.

Approach

8. Utilizing MID Function for a Fixed Number of Digits

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:

Sample dataset of the same no of digits

The steps for this approach are as follows:

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

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

=MID(C6,1,2)&","&MID(C6,3,3)&","&MID(C6,6,3)

๐Ÿ”จ Formula Breakdown

MID(C6,1,2)&โ€,โ€&MID(C6,3,3)&โ€,โ€&MID(C6,6,3)

๐Ÿ‘‰ย  MID(C6,1,2) function selects the string from cell C6, takes the first character as indicated by 1, and selects a group of two characters from the start as stated by the third argument 2.

๐Ÿ‘‰ย  &โ€,โ€& concatenates the previous value and next value with a comma.

๐Ÿ‘‰ย  MID(C6,3,3) function similarly selects the string, takes its third character, and takes a group of three characters.

๐Ÿ‘‰ย  MID(C6,6,3) function selects the string, takes its sixth character, and takes a group of three characters in a similar approach.

Inserting MID Formula

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

Dragging Fill Handle

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

Putting a comma after 3 digits using MID Function

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

Approach

9. Combining LEFT, MID, and RIGHT Functions

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.

=LEFT(C6,2)&","&MID(C6,3,3)&","&RIGHT(C6,3)

๐Ÿ”จ Formula Breakdown

LEFT(C6,2)&โ€,โ€&MID(C6,3,3)&โ€,โ€&RIGHT(C6,3)

๐Ÿ‘‰ย  LEFT(C6,2) extracts the 2 characters on the leftmost part of the string from cell C6.

๐Ÿ‘‰ย  &โ€,โ€& concatenates the previous and next values with a comma.

๐Ÿ‘‰ย  MID(C6,3,3) selects a group of 3 characters from the third character in the string from cell C6.

๐Ÿ‘‰ย  RIGHT(C6,3) selects 3 characters from the rightmost part of the string from cell C6.

Inserting LEFT, MID and RIGHT Functions

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

Dragging Fill Handle

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

Putting a comma after 3 digits using LEFT, MID and RIGHT Functions

Approach

10. Employing a VBA Code to Add Comma

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.

Opening Visual basic editor using the view code option.

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

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

Running the VBA code

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

Putting a comma after 3 digits using VBA code


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.

=MID(C6,1,2)&","&MID(C6,3,6)

๐Ÿ”จ Formula Breakdown

MID(C6,1,2)&โ€,โ€&MID(C6,3,6)

๐Ÿ‘‰ย  MID(C6,1,2) extracts 2 characters starting from the first character as denoted by 1 from cell C6.

๐Ÿ‘‰ย  &โ€,โ€& concatenates the previously obtained value with a comma and the next part.

๐Ÿ‘‰ย  MID(C6,3,6) extracts 6 characters as a group starting from the third character which comes after the comma.

Inserting MID Function

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

Dragging Fill Handle

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

Putting a comma after 2 digits using MID Function


๐Ÿ“„ย  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

(Visited 51 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo