Custom
Last updated
Last updated
If the available options do not meet your specific needs, this option allows you to define a number format. This can be done either by using a custom formatting string or by applying special keywords. This flexibility ensures that you can tailor the number format to match your precise requirements.
Common formatting strings are defined in the Example pop-up. Where none of the examples are suitable you can define your own in the text field below.
A formatting string should be defined for when the field contains ➊ a positive number and ➋ a negative number. The two formatting strings are separated by a semi-colon.
The following characters have special meaning in format strings, all other characters will appear untranslated in the output.
| Character | Meaning |
|---|---|
# | The most used character, the ‘#’ indicates where digits from the source data should appear. |
. | Decimal point. Specifies where the decimal point should appear and, by the use of the ‘#’ character after the point, how many decimal places the number should be formatted to. No rounding will be performed on the value. |
, | May or may not be present as a divider between groups of digits, such as thousands, millions, etc. |
* | Used after the decimal point, this character indicates the minimum number of characters that must appear. For example, you can specify that a field must appear with at least two decimal places, but more will be output if required. |
The use of the formatting string is best explained by the use of examples:
| Format String | Original data | Result | Explanation |
|---|---|---|---|
$#.## | 123456.123456 | $123456.12 | Only two positions are available after the decimal point. |
$###,### | 123456.123456 | $123,456 | The comma may be used to separate groups of digits. No decimal point is provided in the format string, therefore the value will appear as a whole number. |
$#.#### | 123456.123456 | $123456.1234 | Four places are available after the decimal point – the output value is truncated, not rounded. |
# USD | 123456.123456 | 123456 USD | As the characters ‘USD’ do not have any special meaning, they appear untranslated in the output. |
###.##* | 123456.1 | 123456.10 | Here, the position of the ‘*’ character specifies that a minimum of two decimal places are required. |
###.##* | 123456.1234 | 123456.1234 | Here, the position of the ‘*’ character specifies that a minimum of two decimal places are required. |
| Keyword | Description | Example |
|---|---|---|
PREFIX | Specifies the characters to be inserted before the numeric content of the field. | [PREFIX=€] The field will be prefixed with the euro symbol |
SUFFIX | Specifies the characters to be appending to the numeric content of the field. | [SUFFIX=¢] The field will have a ¢ suffix |
DECIMAL | Specifies the decimal separator (radix point) character(s) to use. | [DECIMAL=,] The decimal separator will be the comma - e.g. 199,99 |
PRECISION | Specifies the number of digits that will appear after the decimal point. | [PRECISION=2] Values will be formatted to two decimal places |
THOUSANDS | Specifies a character or characters to use to as the thousands separator. The thousands separator is used to divide the value into groups of three, right-to-left from the decimal point. | [THOUSANDS=.] Values greater than a thousand will use a comma as a thousands separator. e.g.: 1.234.567 |
REMOVE | Characters can be optionally removed when the content of the field matches the specified criteria. | [REMOVE=0.(<1)] For values less than 1, remove “0.” |
The Configure... button can be used to automatically insert these formatting keywords. This feature simplifies the process of defining custom number formats by providing an easy way to input the necessary keywords, ensuring accurate and consistent formatting without manual entry.
