Excel VBA: Format Number with Comma (2 Examples)

How to Write Code in the Visual Basic Editor

• 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 and select New Module

• Put or write your code inside the visual code editor and press F5 to run it.

Method 1 – Format a Number with a Comma as a Thousand Separator with Excel VBA

We have a list of numbers in General number format. We’re going to format them with commas.

Case 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.

Case 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.

• 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

Case 1.3 – Use the Range.NumberFormat property

The syntax of this property is-

expression.NumberFormat = “pre-defined or user-defined number format”

• Use this 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.
  

Read More: How to Use Excel VBA to Format Number in Decimal Places

Method 2 – Use a Comma as a Scalar of Thousands to Format a Number in VBA Excel

• The following code divides the numbers 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.

• 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 1,000,000) after formatting with the number format “#,###,, M”.

Things to Remember

• While using the FormatNumber function in our code, we left 3 arguments blank. The compiler uses the default values in their place.
• To view the code associated with each example, right-click on the sheet name and select View Code.

Download the Practice Workbook

Related Articles

• Excel VBA: Number Format with No Decimal Places
• Excel VBA: Format Currency to Two Decimal Places
• Excel VBA to Format Number with Leading Zero