|
<< Click to Display Table of Contents >> Numeric formats of the Format function |
The following table lists the predefined numeric formats for the Format function:
Format name |
Description |
General Number |
Output the unformatted number. |
Fixed |
Output with at least one digit to the left and exactly two digits to the right of the decimal separator. |
Standard |
Output with at least one digit to the left and exactly two digits to the right of the decimal separator; additionally, the thousands separator is used for numbers >= 1000. |
Percent |
Output with at least one digit to the left and exactly two digits to the right of the decimal separator; additionally, the number is multiplied by 100 and a percent sign is appended. |
Scientific |
Output with at least one digit to the left and exactly two digits to the right of the decimal separator using scientific notation (exponential notation). |
True/False |
"False" if the number is zero, otherwise "True" |
User-defined numeric formats
User-defined numeric formats can be composed of the following characters:
Character |
Meaning |
0 |
Placeholder for digits: Output a digit or zero. |
|
If the number to be formatted has a digit in the position where Format has "0", this digit is output, otherwise 0 is output. If the number to be formatted has fewer digits to the left and to the right of the decimal separator than the number of "0" defined in the Format, leading or trailing zeros are displayed. If the number to be formatted has more digits to the right of the decimal separator than the number of "0" defined in Format, the number will be rounded to the corresponding number of digits. If the number to be formatted has more digits to the left of the decimal separator than the number of "0" defined in Format, the extra digits will always be output. |
# |
Placeholder for digits: Output a digit or nothing. |
|
If the number to be formatted has a digit in the position of "#" in Format, this digit is output, otherwise nothing is displayed. |
. |
Decimal separator |
% |
Percent sign. Causes a percent sign (%) to be output; furthermore, the expression is multiplied by 100. |
, |
Thousands separator. If the number >= 1000, this sign is inserted between the thousands and the hundreds. |
E- E+ e- e+ |
Scientific format. If Format has at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is formatted using a scientific format. This is achieved by inserting an E or e between the mantissa and the exponent. The number of digit placeholders to its right defines the number of digits in the exponent. In case of E+/e+, the exponent is always output with its sign, in case of E-/e- notation the sign is only output if the exponent is negative. |
: |
Time separator. The actual character that is output is defined by the time format in Windows Control Panel. |
/ |
Date separator. The actual character that is output is defined by the date format in Windows' Control Panel. |
- + $ ( ) Space character |
The specified character is output. To output any other character, it must be preceded by a backslash \ or enclosed in quotation marks. |
\ |
The character following the \ is output. The backslash itself is not displayed. To output a backslash, duplicate it (\\). |
|
Note: Quotation marks may not be used in format strings; even \" causes an error message. |
"Text" |
The string enclosed in quotation marks is output. The quotation marks themselves are not displayed. |
* |
Defines the character immediately following as a fill character. Spaces will then be filled using this character. |
User-defined numeric formats can have from one to four sections:
Sections |
Result |
1 section |
This format applies to all values. |
2 sections |
The format in the first section applies to positive values and zero, the one in the second section to negative values. |
3 sections |
The first format applies to positive values, the second one to negative values and the third one to zero. |
4 sections |
The first format applies to positive values, the second one to negative values, the third one to zero and the fourth one to Null values (see the IsNull function). |
If one of these sections is left empty, the format for positive numbers will be used in its place.
The individual sections must be separated by semicolons.
Examples
The following table gives some examples. The left column shows the format expression, the remaining columns show the results for the numbers 3, -3 and 0.3.
Format |
3 |
-3 |
0.3 |
(empty) |
3 |
-3 |
0.3 |
"0" |
3 |
-3 |
0 |
"0.00" |
3.00 |
-3.00 |
0.30 |
"#,##0" |
3 |
-3 |
0 |
"$#,##0;($#,##0)" |
$3 |
($3) |
$0 |
"$#,##0.00;($#,##0.00)" |
$3.00 |
($3.00) |
$0.30 |
"0%" |
300% |
-300% |
30% |
"0.00%" |
300.00% |
-300.00% |
30.00% |
"0.00E+00" |
3.00E+00 |
-3.00E+00 |
3.00E-01 |
"0.00E-00" |
3.00E00 |
-3.00E00 |
3.00E-01 |