Custom

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.

Custom option

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.

Examples

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.

Custom formatting keywords

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.

Configure option panel