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

Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to format the number in Excel using VBA.

Excel VBA to Format Number: 4 Methods

Look at the following example. We stored the same numbers in both Column B and C so that when we format the number in Column C, you will know from the B Column in which format the number was before.

1. VBA to Format Number from One Type to Another in Excel Using VBA NumberFormat Property

First, let’s know how to format the number 12345 from Cell C5 in our given dataset with VBA to Currency format.

• Press Alt + F11 on your keyboard 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.

• Copy the following code and paste it 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 on your keyboard or from the menu bar select Run >> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

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

If you want to show the currency symbol in the cell then simply put the symbol before the code.

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

For our case, we used the dollar ($) symbol. You can use any currency symbol that you want.

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

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

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

2. VBA Format Function to Format Numbers in Different Formats

We will use the VBA Format Function to convert the numbers in a variety of formats. We’ll use the same dataset here as well.

• Following the previous method, create a new Module to write the code.
• Then write this code or copy and paste it from here.
• Next, Press F5 on your keyboard or from the menu bar select Run >> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

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

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

3. Use VBA FormatNumber Function for Different Number Formats

Another function we can use to convert numbers to various format is VBA FormatNumber function.

This function formats a numeric expression as a number and outputs the result as a string.

Syntax of FormatNumber function is:

Let’s go through each of the parameters:

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

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

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

Now, we will use FormatNumber function to convert some numbers into different formats.

• First, we must convert our numbers into Text format as the function takes it as a string. Select range C5:C7 and go to the Home tab.
• Then from the Number group, open the drop-down menu and select Text.

• Following the previous method, create a new Module to write the code.
• Then write this code or copy and paste it from here.
• Next, press F5 on your keyboard or select Run >> Run Sub/UserForm from the menu bar. You can also just click on the small Play icon in the sub-menu bar to run the macro.

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

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

4. Macro to Format A Range of Numbers in Excel

We have seen how to change the number format for a single cell. But if you want to change the format for a range of numbers then the VBA codes are pretty much the same as shown in the above section. This time instead of passing one single cell reference number inside the parentheses of the Range object, you have to pass the whole range (like this C5:C8) inside the brackets.

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

This code will format a specific range of numbers from your dataset in Excel.

Difference Between FormatNumber Function and NumberFormat Property in Excel VBA

FormatNumber is a function used to format numbers as strings in VBA, while NumberFormat is a property used to control the number format of a cell or range in Excel through VBA.

Frequently Asked Questions

1. Is there a way to retrieve or convert formatted numbers back to their original values in VBA?

Ans: No, it is not possible to directly retrieve or convert formatted numbers back to their original values in VBA. Formatting affects only the visual representation of the number, and the original value may not be recoverable without additional information or storing the original value separately.

2. Is there a way to format numbers as ordinal indicators, such as displaying “1st” or “2nd” instead of “1” or “2” in VBA?

Ans: No, VBA does not provide a built-in function to format numbers as ordinal indicators (e.g. 1st, 2nd). You would need to create a custom function in VBA to handle this formatting. The function would take a number as input and return the corresponding ordinal indicator string based on the rules of the English language (e.g. 1st, 2nd, 3rd, etc.).

3. How do I remove formatting from a cell or reset it to the default number format in VBA?

Ans: To remove formatting or reset a cell to the default number format in VBA, you can use the NumberFormat property of the Range object and set it to an empty string. Example:

Sub removeformat()
Range("A1").NumberFormat = ""
End Sub

This code removes any custom formatting from cell A1 and restores it to the default number format.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to format the number in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• How to Change Comma to Dot in Excel
• How to Change International Number Format in Excel
• How to Convert European Number Format to US in Excel
• [Solved] Excel Number Stored As Text
• How to Change Decimals to Percentages in Excel
• How to Convert Percentage to Decimal in Excel
• Excel Number Format Not Working
• [Solved:] Long Numbers Are Displayed Incorrectly in Excel
• How to Display Long Numbers in Excel
• How to Enter 16 Digit Number in Excel
• How to Enter 20 Digit Number in Excel
• How to Convert Exponential Value to Exact Number in Excel