Format Number


s$ = Format$(n,f$)


s$() = ArrFormat$(n(),f$)


This function formats a numeric value (n) as specified by (f$). The ArrFormat$ command performs the same function on every element of an array n(). This function allows a great deal of control over how a number is formatted as a string.

Valid formatting characters are listed in the following table.


Symbol Location Meaning
0 Number Digit.
@ Number Significant digit.
# Number Digit, leading zeroes are not shown.
* Prefix or suffix boundary Pad escape, precedes pad character.
. Number Decimal separator or monetary decimal separator.
- Number Minus sign.
, Number Grouping separator.
E Number Separates mantissa and exponent in scientific notation. Does not need to be quoted in prefix or suffix.
+ Exponent Prefix positive exponents with localized plus sign. Does not need to be quoted in prefix or suffix.
; Subpattern boundary Separates positive and negative subpatterns.
% Prefix or suffix Multiply by 100 and show as percentage.
\u2030 Prefix or suffix Multiply by 1000 and show as per mille.
\u00A4 Prefix or suffix Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator.
' Prefix or suffix Used to quote special characters in a prefix or suffix, for example, "'#'#" formats 123 to "#123". To create a single quote itself, use two in a row: "# o''clock".

A Format pattern contains a positive and negative subpattern, for example, "#,##0.00;(#,##0.00)". Each subpattern has a prefix, a numeric part and a suffix. If there is no explicit negative subpattern, the negative subpattern is the localized minus sign prefixed to the positive subpattern. That is, "0.00" alone is equivalent to "0.00;-0.00". If there is an explicit negative subpattern, it serves only to specify the negative prefix and suffix; the number of digits, minimal digits, and other characteristics are ignored in the negative subpattern. This means that "#,##0.0#;(#)" produces precisely the same result as "#,##0.0#;(#,##0.0#)".


The grouping separator is a character that separates clusters of integer digits to make large numbers more legible. It is commonly used for thousands, but in some locales it separates ten-thousands. The grouping size is the number of digits between the grouping separators, such as 3 for "100,000,000" or 4 for "1 0000 0000". There are actually two different grouping sizes: One used for the least significant integer digits, the primary grouping size, and one used for all others, the secondary grouping size. In most locales these are the same, but sometimes they are different. For example, if the primary grouping interval is 3, and the secondary is 2, then this corresponds to the pattern "#,##,##0", and the number 123456789 is formatted as "12,34,56,789". If a pattern contains multiple grouping separators, the interval between the last one and the end of the integer defines the primary grouping size, and the interval between the last two defines the secondary grouping size. All others are ignored, so "#,##,###,####", "###,###,####" and "##,#,###,####" produce the same result.






Become a Patron