Available for: UI for ASP.NET MVC | UI for ASP.NET AJAX | UI for Blazor | UI for WPF | UI for WinForms | UI for Silverlight | UI for Xamarin | UI for WinUI | UI for ASP.NET Core | UI for .NET MAUI

New to Telerik Document Processing? Download free 30-day trial

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. More information about the predefined types can be found in Number Formatting article.

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" 345 $ 345.00 Salary
$ –0.00" Monthly expenses" 50 $ -50 Monthly expenses
"Invoice for "@ 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 and Conditions

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]

Currency, percentages, and scientific notation

Adding a currency symbol to a number format helps to display numbers as monetary value.

Table 4: Currencies

Number Format Code Value Result
$#,##0 1234 $1,234
#,##0 лв. 1234 1,234 лв.

The Currency format is influenced by your OS regional settings. For more information, go to Localization.

Adding a percent sign (%) in the number format helps to display numbers as a percentage of 100.

Table 5: Percentage

Number Format Code Value Result
0% 0.05 5%
Format negative percentage
0.00%;[Red]-0.00% -0.123 -12.30%

Using one of the exponent codes (E–, E+, e–, or e+) in the number format code helps to display numbers in scientific notation. The number after the exponent code (if any) displays the number in scientific notation.

Table 6: Scientific

Number Format Code Value Result
0.00E+00 123456789 1.23E+08
-1234.56789 -1.23E+03
0.123456789 1.23E-1
0.00e-00 123456789 1.23E08

Date and Time Formatting

The format codes about date and time are listed in the following table:

Table 7

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

The Date and Time formats are influenced by your OS regional settings. For more information, go to Localization.

Localization

Your regional settings will influence how your date/time and currency data types appear when you apply formatting options. You can control these settings from Region & language settings (for Windows) or Language & Region (for Mac).

The regional currency settings determine the position of the currency symbol relative to the number as well as the decimal symbol and the thousands separator. The regional settings also determine the appearance of the date/time data types.

Table 8: Examples

Region Format Result
English (US) Long date format: dddd, MMMM d, yyyy
Long time: h:mm:ss tt
English(UK) Long date format: dd MMMM yyyy
Long time: HH:mm:ss

See Also

In this article