Number Format

The tab contains the list of predefined formats for displaying sheet cells contents. Select required data format and set its parameters. Tab appearance and settings depend on the tool in use.

Setting Up Data Format Parameters

This is the same as removing formatting. Default.

The tab looks as follows when number format is selected:

Set the following parameters:

Decimal Places. Sets the number of displayed decimal places.
Thousands Separator. When this checkbox is selected, the numbers are displayed using decimal separator selected in the current regional settings of the operating system.
Negative Numbers. Select one of the items from this list to determine how negative values should be displayed: with a minus sign or with the word minus.

The tab looks as follows when currency format is selected:

Set the following parameters:

Decimal Places. Sets the number of displayed decimal places.
Symbol. Sets currency symbol.
Negative Numbers. Select format of displaying negative numbers.

Display format depends on the current regional settings selected in the operating system.

The tab looks as follows when the date format is selected:

Determine the date and time format:

Format	Comment
09.10.2004	Day (01-31), month (01-12), 4-digit year number
09.10.04	Day (01-31), month (01-12), 2-digit year number
9 October 2004.	Day (1-31), full month name, 4-digit year number
09 October 2004.	Day (01-31), full month name, 4-digit year number
9 Oct 2004.	Day (1-31), short month name, 4-digit year number
9 Oct 04.	Day (1-31), short month name, 2-digit year number
09 Oct 04.	Day (01-31), short month name, 2-digit year number
9.10.04	Day (1-31), month (01-12), 2-digit year number
9.10.2004	Day (1-31), month (01-12), 4-digit year number
09.10	Day (01-31), month (01-12)
09 Oct	Day (01-31), short month name
2004 October	4-digit year number, full month name
October 2004	Full month name, 4-digit year number
October	Full month name
2004 09 October	4-digit year number, day (01-31), full month name
9-Oct-2004	Day (1-31), short month name, 4-digit year number
09.10.04 18:30	Day (01-31), month (01-12), 2-digit year number, hours (0-24), minutes
09.10.04 6:30 PM	Day (01-31), month (01-12), 2-digit year number, hours (0-12), minutes, AM/PM

The tab looks as follows when the time format is selected:

Determine displayed time format:

Format	Comment
18:30	Hours (24), minutes
6:30 PM	Hours (12), minutes, AM/PM
18:30:00 PM	Hours (24), minutes, seconds
6:30:00 PM	Hours (0-12), minutes, seconds
09.10.04 18:30	Day (01-31), month (01-12), 2-digit year number, hours (0-24), minutes
09.10.04 6:30 PM	Day (01-31), month (01-12), 2-digit year number, hours (0-12), minutes, AM/PM

The tab looks as follows when the percentage format is selected:

Sets the number of displayed decimal places.

If percentage format is used, number values are multiplied by 100 and the "%" character is added. Values entered into a cell are processed in different ways depending on the way the "%" character is used. For example, when 40% is entered, the cell displays 40%, but the cell value is 0.4. When 40 is entered, the cell displays 4000%, but the cell value is 40.

The tab looks as follows when the exponential format is selected:

This format displays all numbers in exponential format.

Determine the number of displayed decimal places.

This is the same as removing formatting. Text format is selected by default for the cells containing non-numeric values.

The tab looks as follows when the additional format is selected:

Select a predefined object to automatically convert value:

Zip code.
Telephone number.
Social security number.

The tab looks as follows when the custom format is selected:

The list of all available formats contains sets of various base formats, using special reserved symbols (format codes). If no format suits you, you can add a custom one.

Display Numbers and Text. To display text and numbers within one cell, specify text characters in double quotes (" "). Use the @ character to show a number without format.

Format

Displayed value

$0.00 "receipt"

$-0.00 "debit"

$1.00 receipt

$-10.00 payable

"Write off "@" units"

Write off 100 units

Digit Capacity and Numbers with Decimal Separator. To form the integer and the fractional part, include the following templates to the section:

#. Only significant numbers are shown, non-significant zeroes are not displayed.
0. Non-significant zeroes are displayed if the number of value positions is less than the number of zeroes in the format.
" " (<space>). If there is space character between # and/or 0, the thousands separator mode is enabled. A number is divided into three numbers by means of spaces. If spaces are inserted after integer part template, each space decreases digit capacity of the displayed number by three, which corresponds to displaying in thousands if there is one space. For example, the "0" template will show "10 000" number as "10".

Fractional part template is set after the "," character.

If the fractional part of a number contains more digits than there are templates in the format, the number is rounded so that the number of positions matches the number of templates. If the integer part of a number contains more digits than there are templates in the format, all significant decimal places are shown.

Examples of number templates:

Format	Initial value	Displayed value
#	5	5
00	5	05
# #	10 1000	10 1 000
#,0#	12 12,557	12,0 12,56
# #,000	150	150,000
#<space>	10000	10
#<space>,00	100	0,10
#,0E+00	1000	1,0E+003

Negative and Zero Numbers. The ";" separator is used to form a separate template for negative and/or zero numbers. Negative number format is after the first character, zero number format is after the second character.

Format	Initial value	Displayed value
# #;-#,00;000	2 -2 0	2 -2,00 000
#;#,0	2 -2	2 2,0

Date and Time. Use format codes to form date and time values:

Date format		Displayed value
Russian	English
М	M	Month displayed as a number between 1 and 12
MM	MM	Month displayed as a number between 01 and 12
MMM	MMM	Month displayed as Jan-Dec
MMMM	MMMM	Month displayed as January-December
Д	D	Day displayed as a number between 1 and 31
ДД	DD	Day displayed as a number between 01 and 31
ДДД	DDD	Day displayed as Mon-Sun
ДДДД	DDDD	Day displayed as Monday-Sunday
ГГ	YY	Year displayed as 01-99
ГГГГ	YYYY	Year displayed as 1900-9999

Time format		Displayed value
Russian	English
Ч	H	Hours displayed as 0-23
ЧЧ	HH	Hours displayed as 00-23
М	M	Minutes displayed as 0-59
MM	MM	Minutes displayed as 00-59
С	S	Seconds displayed as 0-59
СС	SS	Seconds displayed as 00-59
AM/PM	AM/PM	12-hour format is used. AM is the time of day from midnight to midday, while PM is time of day from midday to midnight

The M-MMMM codes should either directly follow the H-HH codes or precede the S-SS codes; otherwise, these codes are displayed as months, not minutes.

Uppercase or lowercase letters can be used to determine format codes.

The language that is used to determine format codes depends on the language of the operating system regional settings. Operating systems with Russian regional settings use the Russian language to specify formats. Operating systems with other regional settings use the English language to specify formats. Changing the language of regional settings automatically changes the format language: Russian/English.

Setting Up Cell Editor

The current settings of the cell editor are displayed and determined in the Cell Editor box. This box is used to determine the type of cell editor to conveniently type in or select required values. Display parameters are determined as a special string containing editor type, default value, and so on. The string can be edited directly in the input box or using the Value Editor Parameters dialog box. Click the Set Up button to open a dialog box. By default, display parameter is selected in accordance with the selected parameter type. To show always active cell editor, deselect the Show Editor Only On Activated Cell checkbox.

NOTE. The cell value, for which the editor is set, should be checked to correspondence to editor data type before installation. The value, which does not correspond to editor data type, is not installed.

NOTE. For each control, a set of pairs "parameter-value" can be determined, which should be separated with a space. For details on control types and their parameters see the method description: IBindingManager.CreateByUi.