Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

Format Codes

RadSpreadProcessing allows to control the appearance of the number values using Number Formats. There is a variety of predefined formats and you can define a custom one in case they are not suitable for your scenario. This article explains how to use the codes in order to create your own number format or modify one of the predefined types.

Overview

A number format could contain up to four sections. Each of them defines a format for different values as shown in Figure 1.

Figure 1: Number format sections

These sections are not required and could be omitted. If only one section is specified, its code is used for all numbers. In case the specified sections are two, the first one is used for positive numbers and zeros and the second one for negative numbers.

If a number format is not explicitly set, the default one is General. It represents an empty string, which has a default behavior for the different types of values.

Format With Text Values

You could display a combination of text and numbers in a cell by enclosing the text in the format string in double quotation marks or, if it is a single character, you could precede it with a backslash (‘\’). The ‘@’ sign is useful when you need to include a text typed from the user in the cell.

Table 1

Number Format Code Value Result
$ 0.00" Salary";$ –0.00" Monthly expenses";;"Invoice for "@ 345 $345.00 Salary
-50 $-50 Monthly expenses
electricity Invoice for electricity

If the format code consists only of an ‘@’ sign, the value in the cell will be visualized as it is typed.

Some characters like the percentage sign (%) do not require using quotation marks when including them in a format code. They are listed in Table 2.

Table 2

$ + - (Minus sign) / ( ) : ! ^
& ` ~ { } > = (Space character)

Decimal Places and Spaces

With the number sign (#) you could display only the significant digits in a number. In order to display non-significant zeros when a number consist of fewer digits than the specified in the format code you could use the numerical character for zero (0).

Table 3

Number Format Code Value Result
####.# 124.49 124.5
#.000 1.2 1.200
0.# .321 0.3
#.0# 11 11.0
123.456 123.46
# ???/??? 1.25 1 1/4
2.5 2 1/2
#,### 12000 12,000
#, 12000 12
00000 12 00012
1234 01234
"000"# 12 00012
1234 0001234

Colors

With the number format codes you can specify the color of a section in the format code. There are eight colors available:

  • Black

  • Blue

  • Cyan

  • Green

  • Magenta

  • Red

  • White

  • Yellow

The name of the color must be defined as the first item in a section and enclosed in square brackets.

Conditions

The number formats could be applied according to conditions. Each condition is enclosed in square brackets and consists of a comparison operator and a value. For example, the following number format will display numbers that are less than or equal to 50 in a red font and numbers that are greater than 50 in a blue font.

[Red][<=50];[Blue][>50]

Date and Time Formatting

The format codes about date and time are listed in Table 4.

Table 4

Number Format Code Value Result
yy Years 00-99
yyyy Years 1900-9999
m Months 1-12
mm Months 01-12
mmm Months Jan-Dec
mmmm Months January-December
mmmmm Months J-D
d Days 1-31
dd Days 01-31
ddd Days Sun-Sat
dddd Days Sunday-Saturday
h Hours 0-23
hh Hours 00-23
m Minutes 0-59
mm Minutes 00-59
s Seconds 0-59
ss Seconds 00-59
h AM/PM Time 6 AM
h:mm AM/PM Time 6:15 PM

See Also