If the format you want to use cannot be created with the given settings, the custom format string allows you to create your own formats using a code explained in the examples below.
The special characters explained below allow you to multiply, divide, separate numbers, etc. Any other characters included in the format string are printed in the resulting data.
Character |
Description |
0 |
Always returns a value for the position it is written in. If there is no number in its place in the data, 0 (zero) will be used. |
# |
Returns values if there are numbers in its place in the data. If used to the left of the decimal point, all digits are returned even if there is one # in the format string and three digits in the data. If used to the right of the decimal point, the same number of digits are returned as there are # to the right of the decimal point, and the number gets rounded up or down. See example below. |
, |
If used before a decimal point, it divides the number in the data by 1000. A difference from Excel is that Excel allows for "," as divider after the decimal point as well. |
% |
Multiplies the number by 100 and inserts a "%" in the number, at the location where it is written in the format string. |
. |
Decimal point. Note: If no decimal point is used and there are decimals in the value you apply the format string on, the value gets rounded up or down. |
; |
Used to divide a format string if different formats are to be used for positive numbers, negative numbers and 0 (zero). If no semicolon is used, the format string is used for all numbers. If one semicolon is used, it divides the format string like this: String for positive numbers and zero;String for negative numbers If two semicolons are used, they divide the format string like this: String for positive numbers;String for negative numbers;String for zero |
\ |
If a "\" is added before a special character that character will not modify the number, the character will only be added to the value. |
Examples:
Note: All these examples use the number 12345.67 as the value from the data.
Format string |
Result |
# #### |
1 2346 |
#.# |
12345.7 |
#.000 |
12345.670 |
|
12.3 |
#,,.## |
.01 |
#% |
1234567% |
#\% |
12345.67% |
$# |
$12346 |
#.##E+0 |
1.23E+4 |
#.#;(#.#) |
12345.7 Had the number been negative, the result would be: (12345.7) |
23 |
23 |
For more information, see literature about custom numeric format strings (for example, on MSDN).