Format Numbers with VBA NumberFormat

The number 1000000000 is difficult to read at a glance, isn’t it? How many zeroes was that? Similarly, the number 12.349845423084 sure contains a lot of decimal digits, but are all those numbers really necessary if you’re looking at a product’s cost-per-unit? Take a quick look at these numbers and tell me how you would compare them.

12.54332
955.3345
1.058383
2342.234

At a glance, they might all seem like they’re of the same order of magnitude, but the decimal point appears in different columns for each number. All the numbers are actually different orders of magnitude.

All these examples illustrate some serious readability concerns, which is a big human performance error trap. To fix these readability issues, use VBA NumberFormat, which is a property of Range objects (the same Range object that controls individual cells in your spreadsheet). In Excel, the main formats are easily accessible via the Number portion of the ribbon:

The most common Excel cell formatting options, displayed in a dropdown

You might have even seen the cryptic number formats under Custom, like these:

Custom Formats Dialogue Box

In this tutorial, we will work with the VBA NumberFormat property, which is a way to automate number formatting in Excel without having to slowly navigate through the screens above. At the end of this tutorial, you’ll understand how these codes work and you’ll be able to write our own in your VBA macros.

• Accessing the VBA NumberFormat Property
• The Format Code
  • Forcing Zeroes
  • Alignment
• Decimal Point and Comma
  • Literals
• VBA NumberFormat Code Sections and Coloring
  • The @ Symbol and Text
• Conditional Formatting
• Fractions
• VBA NumberFormat Symbols
• Conclusion

Assigning and Accessing the VBA NumberFormat Property

Most of this tutorial will focus on how to write the codes themselves. But since this is a VBA blog, we’ll note how to programmatically assign the formats first.

The VBA NumberFormat property is a property of a range object, so you can assign the format to your range like this:

Sub assigning_numberformats()
Dim cell_to_format As Range
Dim vector_to_format As Range
Dim matrix_to_format As Range

Set cell_to_format = Range("A1")
Set vector_to_format = Range("B1:B100")
Set matrix_to_format = Range("C1:D50")

range_to_format.NumberFormat = "YOUR FORMAT CODE HERE" 'replace range and format code

End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Of course, you need to replace range_to_format with the range you actually need to format. In this macro, we’ve predefined three ranges (cell_to_format, vector_to_format , matrix_to_format), but you can use whatever you want.

That’s all there is to assigning the range format. It’s just as easy as clicking the buttons in Excel, except with VBA you can mass-produce your desired results

If you want to access a cell or range’s existing NumberFormat property, you can read it as a string in your macro like this:

the_format = Range("YOUR RANGE HERE").NumberFormat

That’s all there is to it. It really is quite simple to assign and access a NumberFormat using VBA. Now let’s look at the hard part: writing our own format codes.

The Format Code

VBA NumberFormat codes are similar to Regular Expressions in that you define a pattern for the computer to follow. That makes the format dynamic and capable of accepting many inputs.

An expression like 0.0#" per part" might seem a bit odd at first, but it really denotes a pattern. Let’s break it down into its parts:

The Zero (0): there must be at least one digit displayed to the left and one to the right of the decimal

The Pound/Hashtag/Number symbol (#): if there is a significant digit here, it will display, if not, there will be no digit here. Conversely, if there are more than 3 digits to the right of the decimal place, the number will be rounded to two decimal places.

The Quotes (“”): The text between the quotes will always be appended to the numbers entered.

By applying this format code with this line:

Range("A1:A4").NumberFormat = "0.0#"" per part"""

We will end up with these outputs: 2018-09-19-first-one.png

The raw numbers (General format) are on the right and the formatted ones are on the left

Notice what happens here. If we only enter 5, we get back 5.0. But if we enter three digits after the decimal place, our formatted number is rounded to 5.58. It’s important to note that the underlying value stored in cell A3 is still 0.57777. It’s just displayed as 0.58. That’s what readability is all about. We’re not actually changing the numbers; we’re just presenting them in such a way that they’re easier for the user to digest.

See how the words “per part” were appended to every entry? I want you to note the double quotes in our macro If you want text within the format via VBA, make sure to keep track of your quotes, like "Outer Text ""Inner Text"" Outer Text." If you type just one set of quotes around the inner text, VBA will think the string is finished and get confused. This common mistake will throw frustrating errors at you until you satisfy its strict quotation mark requirements. I’ve discussed the complexities of concatenating quotation marks in VBA before. I encourage you to take a look if you want additional guidance.

Here’s a quick test for you: How would our output change if we changed our NumberFormat macro to

Range("A1:A4").NumberFormat = "#.0#"" per part"""

The only difference is the # before the decimal.

Answer: Everything would remain the same, except A3. Remember that # means “display if there, but do not display if not there”. Thus, A3’s number part would change to .58 from 0.58. All the other entries have digits before the decimal point, which are considered significant and will always be displayed.

Forcing Zeros

You might want to force all your numbers to include 3 decimals, regardless of whether the original input had 3 decimals. To do this, we can use “.000”. More than 4 decimal places will round, less than three will result in at least one trailing zero.

Thus 5 will become 5.000 and 5.9337 will become 5.934.

On the other hand, we could write “000.”, which would force a number like 52 to display as 052.

Aligning the Decimal Place

The 0 and # allow us to modify the number of insignificant digits by either forcing them to be displayed, rounding them, or ignoring them altogether. But what about that set of numbers we had earlier with different orders of magnitude? How can we make them easier to compare at a glance? We use the question mark. This number format example

Range("A1:A4").NumberFormat = ".????"

will produce the following result:

The decimal points are now aligned

The decimal points are now aligned, with four decimal places. If there are more than 4 places, they’re rounded (just like # and 0).

Of course, we could achieve the same thing with 0s (".0000"), but our last entry would become “2342.2340”.

Caveat with Alignment

Technically, the question mark (?) is simply a placeholder that adds a space if the digit is missing. It pads the display with spaces when no number is present. Thus, if we center our text above and use the same code, we will get

Unaligned Decimals

To remedy this, you will need to use at least as many ?s as your biggest number has on the left side. Here, we would need "????.???".

The Decimal Point and the Comma

The decimal point obviously denotes where the decimal part of the number begins. If it is omitted, all numbers displayed will be whole numbers. The format 000 will always be at least three digits, but none of them will ever be less than one (neer have a decimal part).

Formatted and Unformatted using 000 as the NumberFormat code

If we have a code like "00.", then the display will always include a decimal point but never any digits after it, like 13..

The Comma

The comma is somewhat more interesting. When I say comma, I really mean whatever your thousands separator is. My visitors from outside the States may be used to seeing a period as the thousands separator.

Anyway, in most formats, it will likely just be the thousands separator: "#,#00.0#" and 0,000.0# will show us

Two different formats with commas as the thousands separator

This is how the comma affects output. We can see the difference with the forced digits (using 0) and optional zeroes and decimals (with #).

This is where things get interesting. The comma also acts as a scaler by a factor of 1000, or three orders of magnitude. To use it in this way, simply append it to the end of your format. To scale by one thousand, use a single comma. To scale by one million, use two commas. You get th idea.

Let’s say we are entering market capitalizations of the biggest companies in the world. Reading a number like 957847923 is a little difficult, but we can easily round to the millions place using this format code "$#,###.00#,, \M". What does this do?

First, it requires at least two decimal places and has an optional third, and it scales the number by one million. It also uses the \ character to add a single letter, M (for millions), and it adds the dollar sign before the number.

The number 957847923 would be displayed as $957.848 M, which is far easier to read. We immediately know it is a dollar amount on the order of millions.

The Literals

Literals show up exactly as typed. Using a backslash, we can add a character literal, such as M (for millions) or C (for Celsius). Most symbols (other than the reserved ones like 0, #, ?, and the like) also act as literals. So the degree symbol, the euro symbol, or the trademark symbol can all be typed into the NumberFormat string and show up on the spreadsheet.

One use of literals that frequently occurs in accounting is showing negative numbers in parentheses. People do this because a minus sign is easy to miss when scanning many numbers. You can simply use "-(0.00) in the “negative section” of the format, which we will look at next. This code utilizes the parentheses literal.

VBA NumberFormat Sections and Coloring

The VBA NumberFormat property has four sections corresponding to positive, negative, zero, and text entries. You separate the four sections using the semicolon.

So "0.0#; [Red]-0.00#; [Blue]0.; [Magenta]@" will format the cell depending on what’s entered. Positive numbers will be black, with at least one and up to two decimals, negative number will be red with the at least two and up to three decimal digits, zero will be blue and simply a zero with a decimal point, and all text will be magenta.

Four types of entries, all formatted differently

The @ Symbol

What’s with the @ at the end of the previous format code? Well, the @ notifies VBA that whatever text is entered there should take the place of the @ symbol. If you leave it out, any text type into the cell won’t appear at all. That means whenever you type out the sections, if you want to include formatting for text, you must include the @ symbol.

On a related not, if your purpose is to format for text (say for coloring or adding information), you can override the number format with a text format, like so:

Sub text_formatter()
Range("A1:A4").NumberFormat = "[Magenta] ""[TEXT:]"" @"
End Sub

Each entry has [TEXT] appended to it and its color changed

The takeaway here is that our entered text (in Column B) is displayed in place of @, and our forced text [TEXT:] precedes it.

Conditional Formatting

You can even use conditional formatting directly in your VBA NumberFormat format codes by using square brackets, just like for the colors. You would separate the conditions with the semicolon.

Sub conditional_number_formats()
Range("A1:A4").NumberFormat = "[>=10][Green]$0.00;[<10][Red]$0.00"
End Sub

This NumberFormat example will force anything with a dollar amount greater than or equal to 10 to green and anything less will be red (negative or between 0 and 10).

Fractions

Decimals are definitely the most common way to displays information, but sometimes the industry standard is to use fractions (in some length measurements, for example). To force fractions, you just need to use the forward slash, like this:

Range("A1:A4").NumberFormat = "[>=10][Green]0 ""dollars and"" 00/100 ""cents"";[<10][Red]0 ""dollars and"" 00/100 ""cents"""

Common way to write check amounts, with a whole dollar amount and a fractional part for cents

Remember to separate the whole number part from the fractional part with at least a space to get mixed fractions. Otherwise you will get a pure fraction. Here’s what I mean. The NumberFormat "0/4" on 2.25 will display 9/4 instead of 2 1/4

The base number can be anything. So if we just wanted to show eighths (as in 5 and 3/8ths inches for a carpentry measurement), we could use this code:

Sub fraction_non_base_ten()
Range("A1:A4").NumberFormat = "00 "" and "" 0/8 ""inches"""
End Sub

which would give us

Fractions with a denominator of 8 and some textual information included

Adding more 0s will increase the accuracy of the fraction. For 0.25, of course, we will get 2/8 exactly. But something like 1.86, using 00/80 in our code, will yield 69/80 instead of 7/8.

VBA NumberFormat Symbols

Before we go, I want to ask you to subscribe using the form below, and then I’m going to leave you with a table you might find helpful when creating your own VBA NumberFormat strings. This table explains each of the common symbols used for formatting numbers and gives a brief example.

Conclusion

This has been a really informative tutorial for working with number formats and the VBA NumberFormat property. It certainly isn’t comprehensive, though. Format codes are quite extensive. For your garden-variety macro, these format codes should be sufficient for you. We’ve shown you how to force numbers to look a certain way, including requiring decimal points and leading zeroes. We’ve also demonstrated how to format text, display fractions, and include conditional formatting in your VBA NumberFormat strings..

The next time you open up the “Custom” cell formatting dialog box, scroll through some of the listed items. They might just make a bit more sense to you now!