Excel VBA: Format Number with Comma (2 Examples)

This article illustrates how to format a number with comma using builtin 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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Examples to Format Number with Comma 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 Comma Using as Thousands Separator with Excel VBA

The most common use of a comma in number formatting is to display thousands separators in a large number. There are several builtin 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 userdefined 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

Excel VBA Format Numbers with Comma

Press F5 to run the code to format the numbers in cells B6:B11 and output them in cells C6:C11.

Excel VBA Format Numbers with Comma


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

Excel VBA Format Numbers with Comma


1.3 Use of the Range.NumberFormat property

Another way to add thousands 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 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

Excel VBA Format Numbers with Comma

Run the code by pressing F5 and the output is shown in the following screenshot.

Excel VBA Format Numbers with Comma

Read More: How to Use Excel VBA to Format Number in Decimal Places (6 Ways)


Similar Readings


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 diving 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

 Excel VBA Format Numbers with Comma

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

Excel VBA Format Numbers with Comma

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: Number Format with No Decimal Places (6 Methods)


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


Conclusion

Now, we know how to format numbers with commas using Excel’s built functions with examples.  Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo