13.2.4 Number Values

The data types Integer, Long, Real, and Money all use a common set of formatting routines. Because of this, only one section is needed here to cover all three data types. The rules and special characters given here work for all of the data types mentioned above.

Formatting numbers uses three specifiers: positive values, negative values, and zero values. In the absence of the appropriate specifier, the positive value specifier (the first one) is used.

While it is easy to create a format, documenting the process is very complex because of all the options available. The next few sections break up the formatting process into its different components. Basically, when a number is formatted, two main things happen: one, the value is converted to a string of ASCII digits; two, the string of digits is edited with special characters to make the number more readable.

The editing can do the following: show the sign of the number, display the currency symbol, separate groups of digits, show where the fractional part of the number begins, insert blanks or other characters as fillers, and round or truncate the fractional portion of the number. For each of these editing operations there is a special character as shown in the table below.

Letter Description

$ Insert currency symbol.

- Insert sign of number (see below).

, Insert thousands separator character.

. Insert decimal separator character

# Insert digit of number or fill character.

0 Insert digit of number and turn off fill.

5 Round to previous digit.

# Truncate after previous digit.

The first section of this table deals with replacement characters. These are characters that must appear as described above in the format string but are replaced in the formatted result by a string or character that has been defined through the use of the COUNTRY setting.

The second section of this table deals with digit insertions. These characters must appear as described above in the format string but are replaced in the formatted result by either a digit of the number or by the fill character (described later).

The last section of the table contains characters to control what is done when the number contains more significant fractional digits than what is contained in the format. Normally, the action is show all significant digits. These characters allow the number to be rounded or truncated at a certain point. These characters are control characters and do not appear in the formatted string.

Fill Characters

The fill character replaces the digit specifier (#) and the thousands group specifier (,) until one of the following conditions is met:

A non-zero digit is inserted for a #.

The forced digit (0) character is encountered.

The decimal separator (.) character is encountered.

The character preceding the first # or 0 in the format is used as the fill character. When the character is a dollar sign ($) or a minus sign (-), it is treated as a floating symbol (discussed later) and the fill character is a space ( ).

When first encountered in the format, the fill character is not inserted into the formatted output. Instead, it is recorded as the fill character to be used later. For example, the number 1234 formatted with *###,##0 produces **1,234 (not ***1,234 as one might expect). The table below gives more examples of fill characters.

Fill

Format Character Value Result

##,### space 1234 1,234

*##,### * 1234 *1,234

*##,### * 123 ***123

$##,### space 123 $123

$ ##,### space 123 $ 123

$*##,### * 123 $***123

-##,### space 123 123

-##,### space -123 -123

- ##,### space -123 - 123

-*##,### * -123 -***123

$-##,### space -123 $ -123

Currency Symbol

Each time the dollar sign ($) appears in a format, it is replaced with the currently defined currency symbol. The dollar sign is allowed in a format only before all digit specifiers (# or 0), or after all digit specifiers. If the dollar sign immediately precedes the first digit specifier, it is treated as a floating dollar sign and inserted before the first non-filled digit. The following examples illustrates the use of the dollar sign:

Format Value Result

$##,### 123 $ 123

$##,### 1234 $ 1,234

$ ##,### 123 $ 123

$ ##,### 1234 $ 1,234

$*##,### 123 $***123

$-##,### -123 $ -123

$-##,### -1234 $-1,234

-##,### $ 1234 1,234 $

-##,### $ -1234 -1,234 $

##$### 123 Not valid

-##,### $ 1234 1.234 L.

As these examples show, the currency symbol can be before the number or after the number but not in the middle of the number.

Conditional Sign

Normally, the negative specifier is used for negative values. However, to make system formats simpler, a shorthand form is allowed. This form uses a conditional sign to indicate a negative number only when the number is actually negative. This sign character must be immediately before the first digit specifier (# or 0), or anywhere after all digit specifiers.

The minus sign (-) is the conditional sign character. If the number is positive or zero, a blank is inserted. If the number is negative, a minus sign is inserted. If the minus sign immediately precedes the first digit specifier, it is treated as a floating sign and inserted before the first non-filled digit. The following examples illustrate the use of the minus sign.

Format Value Result

-##,### 123 123

-##,### -123 -123

-##,### 1234 1,234

-##,### -1234 -1,234

*###,###- 123 ****123

*###,###- -123 ****123-

###-##-#### 123 Not Valid

- ##,### 123 - 123

- ##,### -123 - 123

Notice the last three examples. In the first of these, the format is invalid because the minus sign (-) appears in the middle of the number. This is allowed only with the escape character (###\-##\-####).

Pay close attention to the last two examples. In these examples, the minus sign (-) is not acting as a conditional sign character. To do so, it must appear immediately before the first digit specifier (#). When the minus sign does not act as a conditional sign it is inserted as is, regardless of the actual sign of the number.

Thousands Separator

To help make numbers more readable, separators can be inserted in between groups of digits. For example, American formats insert a comma between every three digits. Other formats may use a different character and different digit groupings. The name of this separator is called the thousands separator.

Every comma (,) in a format string is replaced with either the fill character or (if fill has been turned off) the currently defined thousands separator character. The U.S.A. formats use a comma (,) as the thousands separator character, alternate formats may use a different character (i.e., . for Italian formats). The table below has some examples of using commas.

Format Value Result

##,### 123 123

##,### 1234 1,234

*##,### 123 ***123

-##,### -123 -123

-##,### -1234 -1,234

-##,##,## 12345 1,23,45

-##,### 1234 1.234

Digits and Forced Digits

When a number is to be formatted, it is first converted to a string of digits. This string has enough leading zeros to handle all possible formats. For example, if the number to be formatted is 123.45 with a format of ###,###,##0.00 the string of digits would be 00000012345. The system remembers where the decimal point is but does not include it in the digit string. As the resulting string is built, the leading zeros can either be inserted as is, or they can be replaced with the fill character.

The digit specifier (#) and forced digit specifier (0) indicate where a digit should be placed in the formatted result. This uses one of the digits from the digit string. The number of digit specifiers indicate the number of digits to be shown.

The digit specifier differs from the forced digit specifier in how leading zeros are treated. The digit specifier replaces leading zeros with the fill character or with an actual zero if fill has been turned off. The forced digit specifier always inserts the digit of the number (be it zero or not), and turns off fill. Digit specifiers following a forced digit specifier before the decimal separator (.) act just like a forced digit. For example, the format ##0,000.00 produces the same result as ##0,###.00. Once the first forced digit specifier is encountered, all leading zeros are inserted. The table below gives more examples of digit specifiers.

Format Value Result

##,### 123 123

##,### 1234 1,234

##,### 0

##,##0 123 123

##,##0 1234 1,234

##,##0 0 0

0#,### 123 00,123

0#,### 0 00,000

00,000 123 00,123

As these example show, once the forced digit specifier is used, all leading zeros are inserted as zeros. We do not recommend using this type of format. It makes the number look as though it was truncated on the left.

Decimal Separator

When a number contains a fractional part, you must use a character to indicate where the fractional portion begins. This character is called a decimal separator. A format can either specify this character directly, or the decimal separator can be implied at the end of the digit specifers.

The period (.) in a format is replaced with the currently defined decimal separator character. This also turns off the fill process. After this character, only the forced digit (0) specifier is allowed. Any other character stops the translation of digits. At which point, rounding, truncation, or significance is done followed by the insertion of literal characters from the format string. The table below shows some examples of using the decimal separator.

Format Value Result

#0.00 12.34 12.34

#0.00 .23 0.23

#0.00 12.3456 12.3456

#0.00 .0001 0.0001

##,### 12.34 12.34

As the last example shows, even though the decimal separator is not explicitly in the format it was implied at the end of the digit specifiers and inserted to show all significant digits.

Rounding and Truncation

After the period (.) in a format is processed, two additional characters are recognized. These are the truncation character (#) and the round character (5). These characters control what is done when a Real or Money value contains more decimal places than what is provided for in the format.

The default action is to show all significant digits. For example, a number value of 3.1415926 formatted with #0.000 produces 3.1415926 (all significant digits are shown).

The truncation character (#) is used to truncate decimal digits after the preceding digit. A number value of 3.1415926 formatted with #0.000# produces 3.141 (number truncated after 3 decimal places).

The rounding character (5) is used to round after the previous digit. If the current digit is 5 or greater, the previous digit is incremented. A number value of 3.1415926 formatted with #0.0005 produces 3.142 (number rounded after 3 decimal places).

NOTE: Do not make the mistake of using a format like ###.##. Using this type of format on a number like 12.3456 produces 12.#. The first # after the decimal separator truncates the number. The second # is treated as a literal character and is inserted into the formatted result. The correct format would be any one of the following:

###.00

##0.00 (Forces digit before .)

###.00# (Truncates)

###.005 (Rounds)

Literal Text

When creating a number format, non-special characters cannot be used in between digit specifiers. For example, the format ## . ## is invalid because it contains blanks. Actually the format is valid, but the first blank stops the parsing of the number. The remainder of the string is treated as literal text: formatting 12.34 with above format yields 12.34 . ##.

If non-special characters are wanted in the formatted result, use the escape character (\). The corrected example would be ##\ .\ 00 or ##\ \.\ ## depending on what was intended.

Examples

The table below gives more examples of formatting numbers. This table is by no means exhaustive of all possibilities.

Format Value Result

###,##0 123 123

###,##0 12345 12,345

###,##0 0 0

###,### 0 |

-#,##0 123 123

-#,##0 -123 -123

-#,##0 1234 1,234

-#,##0 -1234 -1,234

-#,##0 0 0

$-#,##0 1234 $ 1,234

$-#,##0 -1234 $ -1,234

$-#,##0 0 $ 0

$##,##0 123 $123

#,##0 ; ( #,##0) 1234 1,234

#,##0 ; ( #,##0) -123 ( 123)

$*#,##0- 1234 $1,234

$*#,##0- -123 $**123-

##.00 13.789 13.789

##.005 13.789 13.79

##.00# 13.789 13.78

##.00 0.1 .10

#0.00 0.005 0.005

#0.005 0.005 0.01

#0.00# 0.005 0.00

#0.00 19.997 19.997

#0.005 19.997 20.00

#0.00# 19.997 19.99

Many other formats are possible, including some that might not be so obvious (like ###\-##\-####, ( ###\)\ ###\-####, or $###,##0.00; $###,##0.00 Cr).