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

Get FREE Advanced Excel Exercises with Solutions!

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`````` • 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:

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

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.
Parameter Values Explanation
vbUseDefault Use the default settings of computer.
• UseParensForNegativeNumbers (optional): A boolean value that determines 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 computer.
• GroupDigits (optional): A boolean value that determines whether to use thousand separator. If omitted, the default is vbUseDefault.
Parameter Values Explanation
vbFalse No Grouping
vbTrue Group Digits.
vbUseDefault Use the default settings of computer.

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

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.

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.

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  