Number format string using #
A number format string consists of a format for positive values and two optional formats for negative and zero values. The format is:
<positive format >{;<negative format >}{;<zero
string>}
The positive format and negative format each represent a number format string as defined below. The zero string is a string that is used when a value is 0 and does not follow the format below. If the zero string is specified, the negative format must also be specified. If the negative format is not defined, it is set to -<positive format >. If the zero string is not defined it is set to "0".
Use this format to define a number format string:
{<prefix>}{#*}{0*}.{0*}{#*}{suffix}
where
- {} represents an optional argument.
- #* represents 0 or more pound signs. The asterisk (*) is used to indicate more than 1.
- 0* represents 0 or more zeros. The asterisk (*) is used to indicate more than 1.
- <prefix> and <suffix> represent any string.
The suffix cannot contain the character being used for a decimal point. The character used for a decimal point can be different if the grid has been set up for a language other than English.
Separator characters can also exist in the string but are not shown in the representation above for simplicity. The separator character is defined as the first character to the left of the decimal point which is not a # or 0 and has a # or 0 to its left.
Scaling
Scaling can be done at the integral end of the number format (immediately to the left of the decimal point). A scaling character represents a scale by 1000. Multiple scaling characters can be specified. For example:
1234567.89
| Number Format | Display String |
|---|---|
| #.0 | 1234567.9 |
| #,, | 1 |
| #,###,.0 | 1,234.6 |
| #,,.0 | 1.2 |
Percent
The percent sign can be added immediately after the last digit format to display the percentage of the value. For example:
0.12
| Number Format | Display String |
|---|---|
| #.0% | 0% |
| #.0%% | 0%% |
Literal sequence
Any characters enclosed inside a quote or following an escape sequence is treated literally. For example:
123.45
| Number format | Display string |
|---|---|
| #.##Hello | 45Hello |
| #.##% | 123.% |
| #.##%% | 12345.%% |
The number of characters between the decimal point and the separator indicate where the separator will appear. Separator characters other than the first separator character to the left of the decimal point are ignored.
When rounding, the IEEE 754 standard's default rounding mode called round to nearest is used. Under some conditions this can result in inexact rounding results. It is possible that a value such as 1.0015 would be treated as 1.001499999. If the number format used were "#.000" the value appears as 1.001 because .0014999 rounds down to .001.
Examples
Some format examples:
| Format string | Value | Formatted value |
|---|---|---|
| ##,#00.00 | 127 | 1,234,567.13 |
| #0,000.00#;(#0,000.00#) | -876.12 | (0,876.12) |
| #0,000.00#;(#0,000.00#);Zero | 0 | Zero |
| $#####.# | 78 | $12345.8 |
| before#.#after | 0 | before1.after |
| $####0 | 0 | $0 |