Custom number patterns
When using the Format Numbers function, you have the possibility to set your custom number pattern for the transformation.
This section provides basic concepts about pattern customization, and some examples to help you understand how those patterns work.
When defining the Target format, select other to define a Custom target format, a Grouping separator and a Decimal separator.
The custom patterns that you can create follow the Java DecimalFormat standard. For more information, see the Java API documentation.
Patterns are built with a positive subpattern and optionally, a negative subpattern. These subpatterns can in turn contain a prefix, a suffix, numbers, fractions and other components listed below:
Syntax
| Component | Can contain |
|---|---|
| Pattern |
PositivePattern
PositivePattern ; NegativePattern |
| PositivePattern | Prefix Number Suffix |
| NegativePattern | Prefix Number Suffix |
| Prefix | Any unicode characters except \uFFFE, \uFFFF, and special characters |
| Suffix | Any unicode characters except \uFFFE, \uFFFF, and special characters |
| Number |
Integer Exponent
Integer . Fraction Exponent |
| Integer |
MinimumInteger
# # Integer # , Integer |
| MinimumInteger |
0
0 MinimumInteger 0 , MinimumInteger |
| Fraction | MinimimFraction OptionalFraction |
| MinimumFraction | 0 MinimumFraction |
| OptionalFraction | # OptionalFraction |
| Exponent | E MinimimExponent |
| MinimumExponent | 0 MinimumExponent |
Characters
The following table lists all the characters that can be used to create custom patterns.
| Character | Meaning |
|---|---|
| 0 | Digit |
| # | Digit, zero shows as absent |
| . | Decimal separator or monetary decimal separator |
| - | Minus sign |
| , | Grouping separator |
| E | Separates mantissa and exponent in scientific notation |
| ; | Separates positive and negative subpatterns |
| % | Multiply by 100 and show as percentage |
| \u2030 | Multiply by 1000 and show as per mille value |
| ¤(\u00A4) | Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator |
| ' | Used to quote special characters in a prefix or suffix, for example, "'#'#" formats 123 to "#123". To create a single quote itself, use two in a row: "# o''clock". |
Examples
Let's take a few examples and see how they will output according to the custom pattern that is used. In this case, space is chosen as grouping separator, and comma as decimal separator
| Input number | Pattern | Result | Comments |
|---|---|---|---|
| 123456,789 | ,##0.0# | 123 456,79 | Digits before the decimals are in group of three, and only 2 decimal digits are shown. If your custom pattern reduces the number of decimal digits, the output will be rounded using half even mode. |
| -123456,789 | ,##0.0# ; (-#) | (-123 456,79) | Any negative pattern that you set will not be used. By default, the positive pattern will be reused but you can still configure a negative pattern to set a prefix and suffix. In this case, the # used in the negative subpattern corresponds to ,##0.0# and the prefix and suffix were added. |
| 12345 | 0.####E0 | 1,2345E4 | - |
| 0.12 | #0% | 12% | - |