Excel VBA: Format Number with Comma (2 Examples)

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.

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.

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.

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.

Format Number with Comma.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!