Number format codes for ZEXMLSS
| Placeholder |
Description |
| 0 (zero) |
Displays zeros if there are fewer digits in the number value than in the number format.
Example: for display value 1.5 as 01.50 use "00.00" format code. |
| . (dot) |
Decimal delimiter. |
| # |
Does not display extra zeros. |
Examples:
| Format |
Cell value |
Result |
| 0.000 |
1.1 |
1.000 |
| #.### |
1.1 |
1.1 |
| 0.000 |
1.12345 |
1.123 |
| #.### |
1.12345 |
1.123 |
To include text in a number format for a cell value, place a text between double quotation marks (") or
use backslash (\) before single character.
Examples:
| Format |
Cell value |
Result |
| 0.00" meters" |
12.14 |
12.14 meters |
| "length="0.00\m |
12.14 |
length=12.14m |
To set the color of a number format code, insert color names in square brackets [ ].
| Color name |
Color as hex |
| [BLACK] |
#000000 |
| [WHITE] |
#FFFFFF |
| [RED] |
#FF0000 |
| [GREEN] |
#00FF00 |
| [BLUE] |
#0000FF |
| [MAGENTA] |
#FF00FF |
| [CYAN] |
#00FFFF |
| [YELLOW] |
#FFFF00 |
To display numbers in a percentage style add percent symbol (%) in format.
To display numbers as fraction, use format like "0 ?/?".
To display numbers as exponential (scientific) notation, add character "E", symbol "+" or "-" and some 0 (zero).
If used symbol "-" in format then result omits symbol "+" for positive value of exponent.
Examples:
| Format |
Cell value |
Result |
| 0.00% |
1.23 |
123% |
| 0 ?/? |
0.5 |
1/2 |
| 0 ?/? |
1.5 |
1 1/2 |
| 0.0E+00 |
12345.58 |
1.2E+04 |
| 0.0E+00 |
0.00052 |
5.2E-04 |
| 0.0E-00 |
12345.58 |
1.2E04 |
| 0.0E-00 |
0.00052 |
5.2E-04 |
Number format codes can be like "part1;part2;part3", where:
- part1 - format code for positive values
- part2 - format code for negative values
- part3 - format code for zero
If number format contains 2 parts ("part1;part2") then:
- part1 - format code for positive values and zero
- part2 - format code for negative values
If needed apply format only in specific conditions, add condition enclosed in square brackets [] to format.
Possible operators: >, >=, <, <=, =, <>.
Examples:
| Format |
Cell value |
Result |
| 0.00" (> 0)";-0.00" (< 0)";0.00" (= 0)" |
100 |
100.00 (> 0) |
| 0.00" (> 0)";-0.00" (< 0)";0.00" (= 0)" |
-100 |
-100.00 (< 0) |
| 0.00" (> 0)";-0.00" (< 0)";0.00" (= 0)" |
0 |
0.00 (= 0) |
| [<0][RED]"O_o";0.00" K" |
273.15 |
273.15 K |
| [<0][RED]"O_o";0.00" K" |
-10 |
O_o |
| Format code |
Description |
| YY |
Year as 00-99 |
| YYYY |
Year as 1900-9999 |
| M |
Month as 5 |
| MM |
Month as 05 |
| MMM |
Month as jan-dec |
| MMMM |
Month as January-December |
| MMMMM |
First letter of month name |
| D |
Day as 6 |
| DD |
Day as 06 |
| DDD |
Day of week as mon-sun |
| DDDD |
Day of week as Monday-Sunday |
| h |
Hour as 7 |
| hh |
Hour as 07 |
| m |
Minutes as 8 |
| mm |
Minutes as 08 |
| s |
Seconds as 9 |
| ss |
Seconds as 09 |
| ss.00 |
Seconds and milliseconds as 09.34 |
| AM/PM |
Time in 12-hour clock convention |
To display cell text in format use character @ (at).
Examples:
| Format |
Cell value |
Result |
| "prefix+"@ |
text |
prefix+text |
| @"+postfix" |
text |
text+postfix |
| @" repeat "@ |
text |
text repeat text |
|
