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 |