Skip to main content Skip to complementary content

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% -

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!