Format Codes
RadSpreadProcessing allows you to control the appearance of number values through Number Formats. A variety of predefined formats exist, and you can define a custom format if they do not suit your scenario. The following sections explain how to use format codes to create your own number format or modify one of the predefined types. For more information about the predefined types, see the Number Formatting article.
Overview
A number format can contain up to four sections. Each section defines a format for different values as shown in Figure 1.
Figure 1: Number format sections
These sections are not required and can be omitted. If only one section is specified, its code is used for all numbers. If two sections are specified, 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 format is General. It represents an empty string, which provides default behavior for the different types of values.
Format with Text Values
You can display a combination of text and numbers in a cell by enclosing the text in the format string in double quotation marks. If it is a single character, you can precede it with a backslash (''). The '@' sign is useful when you need to include text typed by 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 quotation marks when you include them in a format code. The following table lists them.
Table 2
| $ | + | - (Minus sign) | / | ( | ) | : | ! | ^ |
| & | ` | ~ | { | } | < | > | = | (Space character) |
Decimal Places and Spaces
With the number sign (#) you can display only the significant digits in a number. To display non-significant zeros when a number consists of fewer digits than specified in the format code, 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 can 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 displays numbers less than or equal to 50 in a red font and numbers greater than 50 in a blue font.
| [Red][<=50];[Blue][>50] |
Currency, Percentages, and Scientific Notation
Adding a currency symbol to a number format displays numbers as monetary values.
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 displays 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 displays numbers 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 following table lists the format codes for date and time:
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 influence how 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, 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 |