You can set the Format property to predefined number formats or custom number formats for number and currency data types.
Predefined Formats
The following table shows the predefined Format property settings for numbers.
|
Setting |
Description |
|
General Number |
(Default) Display the number as entered. |
|
Currency |
Use the thousand separator; display negative numbers enclosed in parentheses; the default DecimalPlaces property setting is 2. |
|
Fixed |
Display at least one digit; the default DecimalPlaces property setting is 2. |
|
Standard |
Use the thousand separator; the default DecimalPlaces property setting is 2. |
|
Setting |
Description |
|
Percent |
Multiply the value by 100 and append a percent sign (%); the default DecimalPlaces property setting is 2. |
|
Scientific |
Use standard scientific notation. |
Custom Formats
You can create custom number formats using the following symbols.
|
Symbol |
Description |
|
. (period) |
Decimal separator. Separators are set in the Regional Settings section of the Windows Control Panel. |
|
, (comma) |
Thousand separator. |
|
0 |
Digit placeholder. Display a digit or 0. |
|
# |
Digit placeholder. Display a digit or nothing. |
|
$ |
Display the literal character $. |
|
% |
Percentage. The value is multiplied by 100 and a percent sign is appended. |
|
E- or e- |
Scientific notation with a minus sign (-) next to negative exponents and nothing next to positive exponents. It must be used with other symbols, as in 0.00E-00 or 0.00E00. |
|
E+ or e+ |
Scientific notation with a minus sign next to negative exponents and a plus sign (+) next to positive exponents. It must be used with other symbols, as in 0.00E+00. |
Custom number formats can have one to four sections with semicolons (;) as the list separator. Each section contains the format specification for a different type of number.
|
Section |
Description |
|
First |
The format for positive numbers. |
|
Second |
The format for negative numbers. |
|
Third |
The format for zero values. |
|
Fourth |
The format for Null values. |
For example, the standard Currency format has the following pattern.
$#,##0.00;($#,##0.00)[Red]
This number format contains two sections separated by a semicolon and uses one format for positive numbers (determined by the first section) and another for negative numbers (determined by the second section). Since the third and fourth sections are omitted, zero or Null values are not formatted in a special way.
You can use the DecimalPlaces property to display a different number of decimal places than the Format property specifies.
The following are examples of the predefined number formats.
|
Setting |
Data |
Display |
|
General Number |
3456.789 |
3456.789 |
|
Currency |
3456.789 |
$3,456.79 |
|
Fixed |
3456.789 |
3456.79 |
|
Standard |
3456.789 |
3,456.79 |
|
Percent |
3 |
300% |
|
Scientific |
3456.789 |
3.46E+03 |
The following are examples of custom number formats.
|
Setting |
Description |
|
0;(0);; "Null" |
Display positive values normally; display negative values in parentheses; display nothing if the value is zero; display the word “Null” if the value is Null. |
|
+0.0;-0.0;0.0 |
Display a plus (+) or minus (-) sign with positive or negative numbers; display 0.0 if the value is zero or Null. |