Custom formatting of numeric columns
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
If used to the right of the decimal point, the same number of
digits are returned as there are
|
,
|
If used before a decimal point, it divides the number in
the data by 1000.
A difference from Excel is that Excel allows for
" |
%
|
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:
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).