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.
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.
- 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.
Read More: How to Use Comma in Excel Formula
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.
Read More: How to Insert Comma in Excel for Multiple Rows
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.
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.
- 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.
- 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.
Read More: How to Put Comma in Numbers 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.
Similar Readings
- How to Put Comma after 3 Digits in Excel
- How to Put Comma After 5 Digits in Excel
- How to Add Thousand Separator in Excel Formula
- How to Apply Excel Number Format in Thousands with Comma
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.
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.
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.
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.
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.
- 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.
Read More: How to Change Comma Style 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.
- 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.
- Firstly, select the range C5:C9.
- Secondly, from the Developer tab >> select Macros.
- Then, select Macro (AddComma) and click on Run.
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.
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
- How to Insert Comma Between Words in Excel
- How to Add Comma in Excel Between Names
- How to Convert Column to Row with Comma in Excel
- How to Add Comma in Excel at the End
- How to Add Comma Before Text in Excel
- How to Add a Comma Between City and State in Excel
- How to Change Semicolon to Comma in Excel
- How to Change Comma in Excel to Indian Style
- [Fixed!] Style Comma Not Found in Excel
- How to Remove Commas in Excel
