How to Format a Number with VBA in Excel (4 Methods)

In the below dataset, we have the same numbers in Columns B and C.

Method 1 – Using VBA NumberFormat Property

Steps:

Press Alt + F11, or go to the tab Developer >> Visual Basic to open Visual Basic Editor.

In the pop-up code window from the menu bar, click Insert >> Module.

Enter the following code into the code window:

Sub NumberFormat()
    Range("C5").NumberFormat = "#,##0.0"
    'This will format the number 12345 into a currency
End Sub

Your code is now ready to run.

Press F5, or from the menu bar, select Run >> Run Sub/UserForm.

This code will format 12345 into a number with a decimal value.

To show a currency symbol in the cell, enter the symbol before the code.

Sub NumberFormat()
    Range("C6").NumberFormat = "$#,##0.0"
    'This will format the number 12345 into currency with $ symbol
End Sub

We used the dollar ($) symbol.

This code will format the number into currency with a dollar ($) symbol.

You can also convert this format of numbers into many other formats. Just follow the code below to transform the number into your required format.

Sub NumberFormat()
'Original Number 12345
    Range("C5").NumberFormat = "#,##0.0"
    'This will format the number into a currency
    Range("C6").NumberFormat = "$#,##0.0"
    'This will format the number into a currency with $ symbol
    Range("C7").NumberFormat = "0.00%"
    'This will format the number into a percentage value
    Range("C8").NumberFormat = "#,##.00;[red]-#,##.00"
    'This will format the number into red color (Conditional Formatting)
    Range("C9").NumberFormat = "#?/?"
    'This will format the number into fractions
    Range("C10").NumberFormat = "0#"" Kg"""
    'This will format the number with text
    Range("C11").NumberFormat = "#-#-#-#-#"
    'This will format the number with separators
    Range("C12").NumberFormat = "#,##0.00"
    'This will format the number with commas and decimals if applicable
    Range("C13").NumberFormat = "#,##0"
    'This will format the number into thousands with commas if applicable
    Range("C14").NumberFormat = "#,##0.00"
    'This will format the number into millions
    Range("C15").NumberFormat = "dd-mmm-yyyy hh:mm AM/PM"
    'This will format the number into date & time
End Sub

VBA Macro

Overview

Method 2 – Using VBA Format Function

Steps:

Create a new Module.
Enter the following formula:

Sub formatfunction()
'Original Number 12345
    Range("C5") = Format(Range("C5"), "#,##0.00")
    'This will format the number into a currency
    Range("C6") = Format(Range("C6"), "$#,##")
    'This will format the number into a currency with $ symbol
    Range("C7") = Format(Range("C7"), "0.00%")
    'This will format the number into a percentage value
    Range("C8") = Format(Range("C8"), "Scientific")
    'This will format the number in scientific notation
    Range("C9") = Format(Range("C9"), "0#"" Kg""")
    'This will format the number with text
    Range("C10") = Format(Range("C10"), "#-#-#-#-#")
    'This will format the number with separators
    Range("C11") = Format(Range("C11"), "#,##0.00")
    'This will format the number with commas and decimals if applicable
    Range("C12") = Format(Range("C12"), "#,##0")
    'This will format the number into thousands with commas if applicable
    Range("C13") = Format(Range("C13"), "Short Date")
    'This will format the code into Short date
    Range("C14") = Format(Range("C14"), "Long Date")
    'This will format the code into Long date
    Range("C15") = Format(Range("C15"), "Medium Date")
    'This will format the code into Medium date
End Sub

Press F5, or from the menu bar, select Run >> Run Sub/UserForm.

If you have entered the code and run it, this will be your dataset where you can see various numbers formatted.

Method 3 – Using VBA FormatNumber Function for Different Number Formats

Syntax of FormatNumber function is:

FormatNumber( Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits] )

Let’s go through each of the parameters:

Expression (required): This is the numeric value or expression you want to format.
NumDigitsAfterDecimal (optional): Specifies the number of decimal places to display. If omitted, the default is -1, meaning the system settings are used.
IncludeLeadingDigit (optional): A boolean value determining whether a leading zero is displayed for values between -1 and 1. If omitted, the default is vbUseDefault.

Parameter Values	Explanation
vbFalse	No leading zero.
vbTrue	Display leading zero.
vbUseDefault	Use the default settings of the computer.

UseParensForNegativeNumbers (optional): A boolean value determining whether negative numbers are enclosed in parentheses. If omitted, the default is vbUseDefault.

Parameter Values	Explanation
vbFalse	Avoid using parenthesis around negative values.
vbTrue	Put parenthesis around any negative figures.
vbUseDefault	Use the default settings of the computer.

GroupDigits (optional): A boolean value determining whether to use a thousand separator. If omitted, the default is vbUseDefault.

Parameter Values	Explanation
vbFalse	No Grouping
vbTrue	Group Digits.
vbUseDefault	Use the default settings of the computer.

Steps:

Convert the numbers into text format as the function takes them as a string.
Select range C5:C7 and go to the Home tab.
From the Number group, open the drop-down menu and select Text.

Create a new Module.
Enter the following formula:

Sub formatnumberfunction()
    Range("C5") = FormatNumber(Range("C5"), 2)
    'Format the value in cell C5 with 2 decimal places
    Range("C6") = FormatNumber(Range("C6"), 2, , , vbFalse)
    'Format the value in cell C6 with 2 decimal places, using a thousands separator
    'and hiding trailing zeros
    Range("C7") = FormatNumber(Range("C7"), 2, , vbTrue)
    'Format the value in cell C7 with 2 decimal places, using a thousands separator
    'and showing trailing zeros
End Sub

Press F5 on your keyboard or select Run >> Run Sub/UserForm from the menu bar.

If you have entered the code and run it, this will be your dataset where you can see various numbers formatted.

Method 4 – Macro to Format A Range of Numbers in Excel

This time, instead of entering one cell reference number inside the parentheses of the Range object, you have to enter the entire range (i.e., C5:C8) inside the brackets.

Sub NumberFormatRng()
    Range("C5:C8").NumberFormat = "$#,##0.0"
End Sub

Code Breakdown

In this case, the number format applied is “$#,##0.0”

The $ symbol indicates that the numbers will be displayed as currency.
The # symbol is a placeholder for a digit. It will display the actual digits present in the number.
The “,” is the thousands separator, which adds a comma to separate thousands.
The 0 is a placeholder for a digit. It will display the actual digits present in the number, including leading or trailing zeros if present.
The .0 specifies that the number should be displayed with one decimal place.