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

Get FREE Advanced Excel Exercises with Solutions!

When you want to keep data with many digits then you may need to put Comma after 2 digits for easier understandability in Excel. In this article, I will explain how to put Comma after 2 digits in Excel.


How to Put Comma After 2 Digits in Excel: 9 Methods

Here, I’m going to explain 9 methods of how to put Comma after 2 digits in Excel. For your better understanding, I will use a sample dataset. Which has 2 Columns. Those are Product and Quantity. The sample dataset is given below.

How to Put Comma After 2 Digits in Excel


1. Applying LEFT and MID Functions to Put Comma After 2 Digits in Excel

You can apply the LEFT function and the MID function with the Ampersand operator to put Comma after 2 digits in Excel. The steps are given below.

Steps:

  • Firstly, select a different cell D5 where you want to see the result.
  • Secondly, use the corresponding formula in the D5 cell.
=LEFT(C5,2)&","&MID(C5,3,10)

Formula Breakdown

  • LEFT(C5,2)—-> Here, the LEFT function will take the leftmost 2 characters from cell C5.
    • Output: 20
  • The Inverted Comma (“,”) denotes that Comma (,) is a string.
  • MID(C5,3,10)—-> Then, the MID function will extract upto 10 characters starting from the 3rd character in cell C5.
    • Output: 056
  • Finally, Ampersand (&) will join them.
    • Output: 20,056

  • Subsequently, you must press ENTER to get the result.

How to Put Comma After 2 Digits in Excel

  • After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D9.

Finally, you will show the number with Comma after 2 digits.

How to Put Comma After 2 Digits in Excel

Read More: How to Put Comma in Numbers in Excel


2. Use of LEFT, RIGHT and LEN Functions

Also, you can use a combination of some other functions to put Comma after 2 digits in Excel. Here, I will use the LEFT, RIGHT, and LEN functions with the Ampersand operator. You can follow the steps given below.

Steps:

  • Firstly, select a different cell D5 where you want to see the result.
  • Secondly, use the corresponding formula in the D5 cell.
=LEFT(C5,2)&","& RIGHT(C5,LEN(C5)-2)
  • Subsequently, you must press ENTER to get the result.

Formula Breakdown

  • LEFT(C5,2)—-> Here, the LEFT function will take the leftmost 2 characters from cell C5.
    • Output: 20
  • LEN(C5)—-> The LEN function will give the total count of the characters in cell C5.
    • Output: 5
  • RIGHT(C5,5-2)—-> Now, The Right function will take the rightmost 5-2=3 characters of cell C5.
    • Output: 056
  • The Inverted Comma (“,”) denotes that Comma (,) is a string.
  • Finally, Ampersand (&) will join them.
    • Output: 20,056

  • After that, you can double-click the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D9.

Finally, you will see the number with Comma after 2 digits in Excel.

How to Put Comma After 2 Digits in Excel


3. Use of Format Cells Command to Put Comma After 2 Digits in Excel

You can use a built-in command to put Comma after 2 digits in Excel. Which is the Format Cells command. The steps are given below.

Steps:

  •  Firstly, you have to select the data range. Here, I have selected C5:C9.

  • Now, you need to press the CTRL+1 keys to open the Format Cells dialog box directly.
  • Also, you can use the Context Menu Bar or the Custom Ribbon to go to the Format Cells command. In the case of using the Context Menu Bar, select the data range >> Right-Click on the data >> choose the Format Cells option.
  • In the case of using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.

After that, a dialog box named Format Cells will appear.

  • Now, from that dialog box, you have to make sure that you are on the Number command.
  • Then, you need to go to the Custom option.
  • At this time, you have to choose 00″,”00″,”00″,”00 in the Type option.

Also, you can show the written sample instantly in the Sample box.

  • Finally, you must press OK to get the result.

How to Put Comma After 2 Digits in Excel

Finally, you will see the following result.

Furthermore, you can use the Format Cells command to put Comma only after 2 digits in Excel. You can follow the steps given below.

  •  Firstly, you have to select the data range. Here, I have selected C13, C15, and C17. Basically, those cells have similar digit numbers.

How to Put Comma After 2 Digits in Excel

  • Now, you need to press CTRL+1 keys to open the Format Cells dialog box directly.

After that, a dialog box named Format Cells will appear.

  • Now, from that dialog box, you have to make sure that you are on the Number command.
  • Then, you need to go to the Custom option.
  • At this time, you have to choose #,##0 in the Type option.

Here, you should choose this only when you have a 5 digits number. Also, you can show the written sample instantly in the Sample box.

  • Finally, you must press OK to get the result.

  • Now, for cell C14 which has a 6 digit number, you should write 00″,”0000 in the Type section at the Format Cells dialog box.

Here, Inverted Comma will count Comma as a string. Furthermore,  the 1st 2 zero denotes that the Comma lies in the last of the number. In addition, the last 4 zero denotes the position of the Comma from the last of the number.

  • Finally, you must press OK to get the result.

How to Put Comma After 2 Digits in Excel

  • Again, for cell C16 which has a 7 digit number, you should write 00″,”00000 in the Type section at the Format Cells dialog box.

Here, Inverted Comma will count Comma as a string. Furthermore,  the 1st 2 zero denotes that the Comma lies in the last of the number. In addition, the last 5 zero denotes the position of the Comma from the last of the number.

  • Finally, you must press OK to get the result.

Lastly,  you will see all the numbers with Comma after 2 digits.

How to Put Comma After 2 Digits in Excel

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


4. Employing Comma Style Feature

You can employ the Comma Style feature to put Comma after 2 digits in Excel. The steps are given below.

Steps:

  •  Firstly, you have to select the data range. Here, I have selected C5:C9.
  • Secondly, from the Home tab >> you should go to the Number feature.
  • Thirdly, from the Number feature >> choose Comma Style.
  • Finally, you need to Click two times on Decrease Decimal to decrease the decimal.

Here, you can see the result.

How to Put Comma After 2 Digits in Excel

Read More: How to Change Comma Style in Excel


5. Applying Text Function to Put Comma After 2 Digits in Excel

As well as, you can apply the TEXT function to put Comma after 2 digits in Excel.  The steps are given below.

Steps:

  • Firstly, select a different cell D5 where you want to see the result.
  • Secondly, use the corresponding formula in the D5 cell.
=TEXT(C5,"0"",""000")
  • Subsequently, you must press ENTER to get the result.

Formula Breakdown

Here, the TEXT function will convert a value to a text including a certain number format.

  • C5 will denote that value.
  • The last three zero will count the rightmost 3 characters from the value.
    • Output: 056.
  • “0””, —> will add the Comma.
    • Output: 20,056.

  • After that, you can double-click the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D9.

Finally, you will show the number with Comma from the rightmost 3 digits.

How to Put Comma After 2 Digits in Excel

Now, to change the value of cells D6 and D8, you have to write a different formula. Because those numbers have different digits.

  • At this time, write the following formula to the cell D6.
=TEXT(C6,"0"",""0000")
  • Similarly, do the same for the cell D8.
=TEXT(C8,"0"",""0000")

Formula Breakdown

Here, the TEXT function will convert a value to a text including a certain number format.

  • C6 will denote that value.
  • The last four zero will count the rightmost 4 characters from the value.
    • Output: 4505.
  • “0””, —> will add the Comma.
    • Output: 30,4505.

Finally, you will show the number with Comma after 2 digits.

 How to Put Comma After 2 Digits in Excel

Read More: How to Put Comma after 3 Digits in Excel


6. Employing MID Functions with Ampersand Operator

The interesting part is that you can employ only the MID function with the Ampersand operator to put Comma after 2 digits in Excel. The steps are given below.

Steps:

  • Firstly, select a different cell D5 where you want to see the result.
  • Secondly, use the corresponding formula in the D5 cell.
=MID(C5,1,2)&","&MID(C5,3,6)
  • Subsequently, you must press ENTER to get the result.

Formula Breakdown

  • MID(C5,1,2)—-> The MID function will extract 2 characters starting from the 1st character in cell C5.
    • Output: 20
  • The Inverted Comma (“,”) denotes that Comma (,) is a string.
  • MID(C5,3,6)—-> Then, the MID function will extract upto 6 characters starting from the 3rd character in cell C5.
    • Output: 056
  • Finally, Ampersand (&) will join them.
    • Output: 20,056

  • After that, you can double-click the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D9.

Finally, you will show the number with Comma after 2 digits.

 How to Put Comma After 2 Digits in Excel


7. Use of Combined Functions to Put Comma After 2 Digits in Excel

Now, I will use a combined function to put Comma after 2 digits in Excel. Here, I’m going to use the LEFT, MID, and RIGHT functions with the Ampersand operator. The steps are given below.

Steps:

  • Firstly, select a different cell D5 where you want to see the result.
  • Secondly, use the corresponding formula in the D5 cell.
=LEFT(C5,2) &","&MID(C5,3,1)& RIGHT(C5,2)
  • Subsequently, you must press ENTER to get the result.

Formula Breakdown

  • LEFT(C5,2)—-> Here, the LEFT function will take the leftmost 2 characters from cell C5.
    • Output: 20
  • The Inverted Comma (“,”) denotes that Comma (,) is a string.
  • MID(C5,3,1)—-> Then, the MID function will extract 1 character starting from the 3rd character in cell C5.
    • Output: 0
  • RIGHT(C5,2)—-> Now, The Right function will take the rightmost 2 characters of cell C5.
    • Output: 56
  • Finally, Ampersand (&) will join them.
    • Output: 20,056

  • For, 6 digits number you should use the following formula in cell D6.
=LEFT(C6,2) &","&MID(C6,3,2)& RIGHT(C6,2)
  • After that, you must press ENTER to get the result.

 How to Put Comma After 2 Digits in Excel

Formula Breakdown

  • LEFT(C6,2)—-> Here, the LEFT function will take the leftmost 2 characters from cell C6.
    • Output: 30
  • The Inverted Comma (“,”) denotes that Comma (,) is a string.
  • MID(C6,3,2)—-> Then, the MID function will extract 2 characters starting from the 3rd character in cell C6.
    • Output: 45
  • RIGHT(C6,2)—-> Now, The Right function will take the rightmost 2 characters of cell C6.
    • Output: 05
  • Finally, Ampersand (&) will join them.
    • Output: 30,4505

  • After that, you can double-click the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D9. Because those cells contain 6 digit number.

Finally, you will see the number with Comma after 2 digits in Excel.


8. Applying Currency Feature

You can employ the Currency feature to put Comma after 2 digits in Excel. The steps are given below.

Steps:

  •  Firstly, you have to select the data range. Here, I have selected C5:C9.
  • Secondly, from the Home tab >> you should go to the Number feature.
  • Thirdly, in the Number feature >> Click on the Drop-Down Arrow >> At this time, you need to choose Currency.

 How to Put Comma After 2 Digits in Excel

  • Finally, from the Number feature >> you need to Click two times on Decrease Decimal to decrease the decimal.

Now, you can see the result whose have Dollar Sign.

 How to Put Comma After 2 Digits in Excel


9. Using VBA Code to Put Comma After 2 Digits in Excel

You can employ a VBA code to put Comma after 2 digits in Excel. The steps are given below.

Steps:

  • Firstly, you have to choose the Developer tab >> then select Visual Basic.

  • Now, from the Insert tab >> select Module.

 How to Put Comma After 2 Digits in Excel

  • Write down the following Code in the Module.
Sub AddComma()
Dim data_range As Range
Dim digit1 As Long
Dim digit2 As Long
Dim num As String
Dim digit3 As Long
Application.ScreenUpdating = False
For Each data_range In Selection
If IsNumeric(data_range.Value) Then
If data_range.Value < 0 Then
digit3 = 2
Else
digit3 = 1
End If
digit2 = Len(data_range.Value)
num = ""
For digit1 = digit2 - 2 To digit3 Step -2
num = """,""00" & num
Next digit1
If digit2 Mod 2 Or digit3 = 2 Then
num = "00" & num
Else
num = "00" & num
End If
data_range.NumberFormat = num
End If
Next data_range
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • Here, I have created a Sub Procedure named AddComma.
  • Next, declare some variables data_range as Range to call the range; digit1 as Long; digit2 as Long; num as String; digit3 as Long.
  • Then, the Selection property will select the range from the sheet.
  • After that, I used a For Each Loop to put Comma in each selected cell using a VBA If function to do a logical test.

  • Now, Save the code then go back to Excel File.

 How to Put Comma After 2 Digits in Excel

  • Firstly, select the range C5:C9.
  • Secondly, from the Developer tab >> select Macros.

  • Then, select Macro (AddComma) and click on Run.

 How to Put Comma After 2 Digits in Excel

Finally, you will see the following result.


Things to Remember

  • In the case of the Comma Style feature, the Comma will be counted as for thousand format. So, when you have numbers of 5 digits then you can easily use this method.
  • Also, in the case of using the Currency feature, the Comma will be counted as for thousand format.
  • Moreover, when you have digit variation in numbers then you have to change the formula as per requirement.
  • In addition, the 1st method is the easiest method to put a Comma after 2 digits to any digits of numbers.

Practice Section

Now, you can practice by yourself.

 How to Put Comma After 2 Digits in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 9 different ways to Put Comma After 2 Digits in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.


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.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo