This article illustrates how to format a number with commas using built-in functions in VBA Excel. We commonly use a comma to display a thousand separators in large numbers. There is another use of commas in number formatting as scalars of thousands. Let’s dive into the examples to understand the uses clearly.
2 Examples of Format Numbers with Commas Using VBA in Excel
The following section describes how to open and write code in the Visual Basic Editor.
Write Code in Visual Basic Editor
Follow the steps to open the Visual Basic Editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module
Now put your code inside the visual code editor and press F5 to run it.
Read More: Excel VBA to Format Number with Leading Zero
1. Format a Number with a Comma Using as Thousands Separator with Excel VBA
The most common use of a comma in number formatting is to display thousands of separators in a large number. There are several built-in functions to use in our VBA code to make it happen. Here we have a list of numbers in General number format. We’re going to format them with commas using the following functions.
1.1 Use of the Format Function
The Format function in VBA code converts numbers into strings that are formatted according to predefined or user–defined format codes. The syntax of the function is-
Format(expression, [format], [firstdayofweek],[firstweekofyear])
Here, expression- any valid expression. We can insert a number or Range object as the argument.
format- a valid number format (predefined or user-defined)
Copy and paste the following code to format the numbers in our sample dataset with commas.
Sub FormatNumberWithComma()
Range("C6") = Format(Range("B6"), "#,###")
Range("C7") = Format(Range("B7"), "#,###")
Range("C8") = Format(Range("B8"), "#,###.00")
Range("C9") = Format(Range("B9"), "#,##0.00")
Range("C10") = Format(Range("B10"), "#,###")
Range("C11") = Format(Range("B11"), "#,###")
End Sub
Press F5 to run the code to format the numbers in cells B6:B11 and output them in cells C6:C11.
Read More: How to Use Excel VBA to Format Number in Decimal Places
1.2 Apply the VBA FormatNumber Function
We can use the VBA FormatNumber function to format a number by defining different criteria. The syntax of the function is-
FormatNumber(Expression, [NumberDigitsAfterDecimal, [IncludeLeadingDigit, [UseParensForNegetiveNumbers, [GroupDigits]]]])
To display thousands of separators in a number, we need to set the last argument [GroupDigits] as vbTrue. This argument represents whether the numbers are grouped or not using the group delimiter. Normally, the group delimiter is a comma, specified in the computer’s regional settings.
Now copy and paste the following VBA code to add thousands of separators to our sample dataset i.e., cells B6:B11.
Sub FormatNumberWithComma()
Range("C6") = FormatNumber(Range("B6"), , , , vbTrue)
Range("C7") = FormatNumber(Range("B7"), , , , vbTrue)
Range("C8") = FormatNumber(Range("B8"), , , , vbTrue)
Range("C9") = FormatNumber(Range("B9"), , , , vbTrue)
Range("C10") = FormatNumber(Range("B10"), , , , vbTrue)
Range("C11") = FormatNumber(Range("B11"), , , , vbTrue)
End Sub
1.3 Use of the Range.NumberFormat property
Another way to add thousands of separators to a number is to use Excel’s Range.NumberFormat property. The syntax of this property is-
expression.NumberFormat = “pre-defined or user-defined number format”
Let’s use this property in our code to format cells B6:B11 with commas to add thousands of separators.
Sub FormatNumberWithComma()
Range("B6").NumberFormat = "#,###"
Range("B7").NumberFormat = "#,###"
Range("B8").NumberFormat = "#,###.00"
Range("B9").NumberFormat = "#,##0.00"
Range("B10").NumberFormat = "#,###"
Range("B11").NumberFormat = "#,###"
End Sub
Run the code by pressing F5 and the output is shown in the following screenshot.
2. Use Comma as Scalers of Thousands to Format a Number in VBA Excel
If we use a comma after the numeric part of a number code, it will display the formatted number after dividing it by 1000. To illustrate this, in the following example, we’ll use a single comma after the numeric part of the number code i.e., “#,###,.00”. The following code divides the numbers, that are in cells B6:B11, by a thousand and prints the output in cells C5:C11.
Sub FormatNumberWithComma()
Range("C6") = Format(Range("B6"), "#,###,.00")
Range("C7") = Format(Range("B7"), "#,###,.00")
Range("C8") = Format(Range("B8"), "#,###,.00")
Range("C9") = Format(Range("B9"), "#,##0,.00")
Range("C10") = Format(Range("B10"), "#,###,.00")
Range("C11") = Format(Range("B11"), "#,###,.00")
End Sub 
In the output, we see that all the numbers in cells B6:B11 got divided by 1000 in cells C6:C11.
Now let’s use 2 commas in the number code after the numeric part. The code is here below.
Sub FormatNumberWithComma()
Range("C6") = Format(Range("B6"), "#,###,, M")
Range("C7") = Format(Range("B7"), "#,###,, M")
Range("C8") = Format(Range("B8"), "#,###,, M")
Range("C9") = Format(Range("B9"), "#,###,, M")
End Sub
The numbers are in the millions (divided by 1000,000) after formatting with the number format “#,###,, M”. that’s why using commas after the numeric part in format code as scalars of thousands.
Read More: Excel VBA: Format Percentage to 2 Decimal Places
Things to Remember
- While using the FormatNumber function in our code, we left 3 arguments blank. As a result, it would apply the default value in execution.
- To view the code associated with each example, click the right button on the sheet name and select the View Code
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to format numbers with commas using Excel’s built-in functions with examples. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
