9 Tricks to Put Comma After 2 Digits in Excel

In this article, I’ll show you three excellent methods on how to put a comma after every 2 digits in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Put a Comma After Every 2 Digits in Excel with These 9 Useful Techniques

Here in this article, we will learn how to put a comma after 2 digits in Excel in Excel using different approaches. To be exact, I’ve provided a total of 9 methods down below. We took the Inventory data of a company as our dataset contains only two columns as Products and Quantities. The quantity of the product is so high that it is difficult for the accountant to keep track. So putting a comma after two digits will be advisable.

Firstly, we will employ the Combination of LEFT with MID function to a insert comma every in the gap of every 2 digits.

In Excel, LEFT function returns a predetermined number of characters from the start of a text string. The MID function extracts a predetermined number of characters starting at a predetermined location from a text string.

However, using LEFT function to extract a section of the text string and MID function on the resulting text to extract another section is how the MID and LEFT functions are combined.

Let’s explore this approach step by step.

⬇️⬇️ STEPS ⬇️⬇️

• First and foremost, select cell D6.
• Insert the following formula:

• Press Enter and drag fill handle to see the results.

In this way, you can put comma in the gap of every 2 digits.

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

Now we will use the Combination of LEFT, RIGHT, and LEN function options to do the same task.

LEFT function in Excel returns a certain number of characters from the beginning of a text string. The RIGHT function returns a certain number of characters from the end of a text string. The LEN function returns the number of characters in a text string.

To combine the LEFT with RIGHT, and also LEN functions, you could employ the LEN function to find out how many characters are in a text string and then employ the LEFT or RIGHT function to get a part of the text based on that length. We will use this info to insert comma in our number.

Steps are shown below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select cell D6.
• Write down the following formula:

• Finally, press Enter and drag fill handle to see the results.

That’s how you can put comma in the gap of every 2 digits.

 Read More: 10 Ways to Put a Comma After 3 Digits in Excel

Now we will use Format Cells Feature to do the same task. With Excel’s Format Cells function, you may alter the look of a single cell or a selection of cells in a worksheet. It allows you to format text in cells by altering things like font, size, color, alignment, borders, fill color, and number format. To make your data more legible and attractive, use Format Cells feature. Create polished, user-friendly spreadsheets with its guidance.

Steps are shown below.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells where you want to insert comma.

• Select Home tab > Format feature > Format Cells option.

• Select Custom option and input “#,##0” into the format type box.

• Finally, select OK.

Accordingly, you can insert comma in the gap of every 2 digits.

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

Finally, we used the Comma Style Wizard in this technique. Excel’s Comma Style format makes huge numbers simpler to read by inserting a thousand divider (a comma) between digits. Applying the Comma Style format to a cell or a range of cells causes Excel to insert a comma after every three decimal places. Using comma style format makes it much simpler to grasp the scope of the numbers and see trends or patterns in the data, especially when dealing with enormous amounts of information. We explain this method in detail.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells where you want to insert a comma.
• Select Comma style from the Number menu of the Home tab.
• Click twice on the Decimal Decrease to convert it to zero decimal places.

• Finally, you may see the results.

Hence, you will be able to insert a comma a comma in the gap of every 2 digits.

 Read More: 5 Tricks to Insert Comma Between Words in Excel

Finally, we used Text function to do this task. You can use Excel’s TEXT function to convert a numeric or date value to text in a specified number format. If you wish to utilize TEXT function output in a calculation, you’ll need to change it back to a number first. If you require to show numerical values separated by commas in a specified format, as in a report or a dashboard, you can use TEXT function for your advantage. It can also come in handy if you require to transfer or import data to or from a system that has strict numerical format requirements. Steps are shown below:

⬇️⬇️ STEPS ⬇️⬇️

• First of all, select cell D6.
• Write down the given formula:

• Finally, press Enter and drag fill handle to see the results.

Thus, you can insert a comma a comma in the gap of 2 digits.

Here, we used only MID function. You can use Excel’s MID function to take off a set number of characters from the beginning of a string of text or number at a given index. Later it helps out to insert a comma in a number as a thousand separator.  Here I demonstrated the process step by step.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell D6.
• Following that, insert the following formula:

• Now press Enter and drag fill handle to see the output.

Thus, you can insert a comma after two digits.

• Finally, press Enter and drag fill handle to see the results.

In this way, you can insert a comma in the gap of 2 digits.

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

Finally, we used the Combination of LEFT, RIGHT, and MID functions in this approach. Combinations of the LEFT, RIGHT, and MID functions in Excel allow for extensive manipulation of text strings or numbers. LEFT function may be used to take off a specified number of characters of the start of a string or number, the RIGHT function can do the same for the end, and the LEN function can find out how many characters there are in the string as a whole. When used together, these functions allow you to isolate and alter individual characters inside a number. So using these features of the function makes it possible to put comma in a number. I will show the steps of the technique one by one.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell D6.
• Later insert the given formula:

• Press Enter and drag fill handle to see the results.

In this way, you will be able to a insert comma after every 2 digits.

Finally, we used the Currency Option in this technique. You can add a comma as a thousand separator and format a cell or a range of cells as currency in Excel with the currency option. When you apply a currency format to a cell, it will show the currency symbol, such $ or €, and the number will be formatted with the correct amount of decimal places and a comma to separate thousands. The amount of digits displayed after the decimal point is likewise customizable. Note that Excel’s currency formatting does not affect the actual value of the cell, simply how the number is shown. Here, I gave a detailed explanation of the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• First of all, select the cells where we will insert a comma after two digits.

• Choose the Currency option from Number wizard of the Home tab.

That’s how you will be able to a insert comma after every 2 digits.

Finally,e will use VBA Option to solve this technique. Excel the VBA macros are a collection of instructions that can be used to automate various operations, including data preparation, chart creation, and calculation. Examples of VBA macro functionality include the creation of a new worksheet with pre-populated data taken from another worksheet depending on certain criteria and automatically formatting a set of cells each time the workbook is accessed. The steps are given in proper sequence below

⬇️⬇️ STEPS ⬇️⬇️

• Take the cursor to the sheet name and click right.
• Select the option View code.
• Insert the following VBA code:

Sub AddingComma()
Dim data_range As Range
Dim num1 As Long
Dim num2 As Long
Dim num As String
Dim num3 As Long
Application.ScreenUpdating = False
For Each data_range In Selection
If IsNumeric(data_range.Value) Then
If data_range.Value < 0 Then
num3 = 2
Else
num3 = 1
End If
num2 = Len(data_range.Value)
num = ""
For num1 = num2 - 2 To num3 Step -2
num = """,""00" & num
Next num1
If num2 Mod 2 Or num3 = 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

• Run the code after selecting the range to see the output.

Hence,  you will be able to insert a comma after every 2 digits.

📄 Important Notes

🖊️  Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.

🖊️  In addition, always look for ways that are easy to use in different situations.

🖊️  Finally, every time you try to use shortcuts on the keyboard.

📝 Takeaways from This Article

📌  Firstly you can insert a comma after every 2 digits using the LEFT, RIGHT and MID functions.

📌  In addition, You can insert a comma after every 2 digits using the TEXT function.

📌  Finally, you can insert a comma after every 2 digits using VBA macro.

Conclusion

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to put a comma every after 2 digits in Excel. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.

Related Article

• 5 Easy Ways to Add Comma in Excel to Concatenate Cells