Number Patterns
When defining data type options for numeric data, you can create your own custom number pattern if the predefined ones do not meet your needs. A basic number pattern consists of the elements below:
- A prefix such as a currency symbol (optional)
- A pattern of numbers containing an optional grouping character (for example a comma as a thousands separator)
- A suffix (optional)
For example, this pattern:
$ ###,###.00
Would produce a number formatted like this (note the use of a thousands separator after the first three digits):
$232,998.60
Patterns for Negative Numbers
By default, negative numbers are formatted the same as positive numbers but have the negative sign added as a prefix. The character used for the number sign is based on the locale. The negative sign is "-" in most locales. For example, if you specify this number pattern:
0.00
The number negative ten would be formatted like this in most locales:
-10.00
However, if you want to define a different prefix or suffix to use for negative numbers, specify a second pattern, separating it from the first pattern with a semicolon (";"). For example:
0.00;(0.00)
In this pattern, negative numbers would be contained in parentheses:
(10.00)
Scientific Notation
If you want to format a number into scientific notation, use the character
E followed by the minimum number of digits you want to include in the
exponent. For example, given this pattern:
0.###E0
The number 1234 would be formatted like this:
1.234E3
In other words, 1.234 x 103.
Note that:
- The number of digit characters after the exponent character gives the minimum exponent digit count. There is no maximum.
- Negative exponents are formatted using the localized minus sign, not the prefix and suffix from the pattern.
- Scientific notation patterns cannot contain grouping separators (for example, a thousands separator).
Special Number Pattern Characters
The characters below render other characters, as opposed to being reproduced literally in the resulting number. If you want to use any of these special charters as literal characters in your number pattern's prefix or suffix, surround the special character with quotes.
| Symbol | Description |
|---|---|
|
|
Represents a digit in the pattern including zeros where needed to fill in the pattern. For example, the number twenty-seven when applied to this pattern:
Would be: 0027 |
|
|
Represents a digit but zeros are omitted. For example, the number twenty-seven when applied to this pattern:
Would be: 27 |
|
|
The decimal separator or monetary decimal separator used in the selected locale. For example, in the U.S. the dot (.) is used as the decimal separator but in France the comma (,) is used as the decimal separator. |
|
|
The negative sign used in the selected locale. For most locals this is the minus sign (-). |
|
|
The grouping character used in the selected locale. The appropriate character for the selected locale will be used. For example, in the U.S., the comma (,) is used as a separator. The grouping separator is commonly used for thousands, but in some countries it separates ten-thousands. The grouping size is a constant number of digits between the grouping characters, such as 3 for 100,000,000 or 4 for 1,0000,0000. If you supply a pattern with multiple grouping characters, the interval between the last one and the end of the integer is the one that is used. For example, all the following patterns produce the same result:
|
|
|
Separates mantissa and exponent in scientific notation. You do not need to surround the E with quotes in your pattern. See Scientific Notation. |
|
|
Separates positive and negative subpatterns. See Patterns for Negative Numbers. |
|
|
Multiply the number by 100 and show the number as a percentage. For example, the number .35 when applied to this pattern:
Would produce this result: 35% |
|
|
The currency symbol for the selected locale. If doubled, the international currency symbol is used. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. |
|
|
Used to quote special characters in a prefix or suffix. For example,
Formats 123 to:
To create a single quote itself, use two in a row:
|