Custom numeric format strings

You can create a custom numeric format string, which consists of one or more custom numeric specifiers, to define how to format numeric data. A custom numeric format string is any format string that isn't a standard numeric format string.

The following table describes the custom numeric format specifiers and displays sample output produced by each format specifier. Go to the Notes section for additional information about using custom numeric format strings, and the Example section for a comprehensive illustration of their use.

The following sections provide detailed information about each of the custom numeric format specifiers.

The "0" custom specifier

The "0" custom format specifier serves as a zero-placeholder symbol. If the value that is being formatted has a digit in the position where the zero appears in the format string, that digit is copied to the result string; otherwise, a zero appears in the result string. The position of the leftmost zero before the decimal point and the rightmost zero after the decimal point determines the range of digits that are always present in the result string.

The "00" specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "00" would result in the value 35.

The following example displays several values that are formatted by using custom format strings that include zero placeholders.

let
    Source =
    {
        Number.ToText(123, "00000", ""),
        // Displays 00123

        Number.ToText(1.2, "0.00", ""),
        // Displays 1.20

        Number.ToText(1.2, "00.00", ""),
        // Displays 01.20

        Number.ToText(1.2, "00.00", "da-DK"),
        // Displays 01,20

        Number.ToText(.56, "0.0", ""),
        // Displays 0.6

        Number.ToText(1234567890, "0,0", ""),
        // Displays 1,234,567,890

        Number.ToText(1234567890, "0,0", "el-GR"),
        // Displays 1.234.567.890

        Number.ToText(1234567890.123456, "0,0.0", ""),
        // Displays 1,234,567,890.1

        Number.ToText(1234.567890, "0,0.00", "")
        // Displays 1,234.57
    }
in
    Source

Back to table

The "#" custom specifier

The "#" custom format specifier serves as a digit-placeholder symbol. If the value that is being formatted has a digit in the position where the "#" symbol appears in the format string, that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string.

Note that this specifier never displays a zero that isn't a significant digit, even if zero is the only digit in the string. It displays zero only if it's a significant digit in the number that is being displayed.

The "##" format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "##" would result in the value 35.

The following example displays several values that are formatted by using custom format strings that include digit placeholders.

let
    Source =
    {
        Number.ToText(1.2, "#.##", ""),
        // Displays 1.2

        Number.ToText(123, "#####"),
        // Displays 123

        Number.ToText(123456, "[##-##-##]"),
        // Displays [12-34-56]

        Number.ToText(1234567890, "#"),
        // Displays 1234567890

        Number.ToText(1234567890, "(###) ###-####")
        // Displays (123) 456-7890
    }
in
    Source

To return a result string in which absent digits or leading zeroes are replaced by spaces, use the Text.PadStart and specify a field width, as the following example illustrates.

let
   Source = Text.Format("The value is: '#{0}'", {Text.PadStart(Number.ToText(.324, "#.###"), 5)})
in
   Source

// The example displays the following output if the current culture
// is en-US:
//      The value is: ' .324'

Back to table

The "." custom specifier

The "." custom format specifier inserts a localized decimal separator into the result string. The first period in the format string determines the location of the decimal separator in the formatted value; any additional periods are ignored. If the format specifier ends with a "." only the significant digits are formatted into the result string.

The character that is used as the decimal separator in the result string isn't always a period; it's determined by the culture that controls formatting.

The following example uses the "." format specifier to define the location of the decimal point in several result strings.

let
    Source =
    {
        Number.ToText(1.2, "0.00", ""),
        // Displays 1.20

        Number.ToText(1.2, "00.00", ""),
        // Displays 01.20

        Number.ToText(1.2, "00.00", "da-DK"),
        // Displays 01,20

        Number.ToText(.086, "#0.##%", ""),
        // Displays 8.6%

        Number.ToText(Double.From(86000), "0.###E+0", "")
        // Displays 8.6E+4
    }
in
    Source

Back to table

The "," custom specifier

The "," character serves as both a group separator and a number scaling specifier.

• Group separator: If one or more commas are specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output.

  The culture determines the character used as the number group separator and the size of each number group. For example, if the string "#,#" and the invariant culture are used to format the number 1000, the output is "1,000".

• Number scaling specifier: If one or more commas are specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 for each comma. For example, if the string "0,," is used to format the number 100 million, the output is "100".

You can use group separator and number scaling specifiers in the same format string. For example, if the string "#,0,," and the invariant culture are used to format the number one billion, the output is "1,000".

The following example illustrates the use of the comma as a group separator.

let
    Source =
    {
        Number.ToText(1234567890, "#,#", ""),
        // Displays 1,234,567,890

        Number.ToText(1234567890, "#,##0,,", "")
        // Displays, 1,235
    }
in
    Source

The following example illustrates the use of the comma as a specifier for number scaling.

let
    Source =
    {
        Number.ToText(1234567890, "#,,", ""),
        // Displays 1235

        Number.ToText(1234567890, "#,,,", ""),
        // Displays 1

        Number.ToText(1234567890, "#,##0,,", "")
        // Displays 1,235
    }
in
    Source

Back to table

The "%" custom specifier

A percent sign (%) in a format string causes a number to be multiplied by 100 before it's formatted. The localized percent symbol is inserted in the number at the location where the % appears in the format string. The percent character used is defined by the culture.

The following example defines a custom format string that includes the "%" custom specifier.

let
    Source = Number.ToText(.086, "#0.##%", "")
    // Displays 8.6%
in
    Source

Back to table

The "‰" custom specifier

A per mille character (‰ or \u2030) in a format string causes a number to be multiplied by 1000 before it's formatted. The appropriate per mille symbol is inserted in the returned string at the location where the ‰ symbol appears in the format string. The per mille character used is defined by the culture, which provides culture-specific formatting information.

The following example defines a custom format string that includes the "‰" custom specifier.

let
    Source = Number.ToText(.00354, "#0.##" & Character.FromNumber(0x2030), "")
    // Displays 3.54‰
in
    Source

Back to table

The "E" and "e" custom specifiers

If any of the strings "E", "E+", "E-", "e", "e+", or "e-" are present in the format string and are followed immediately by at least one zero, the number is formatted by using scientific notation with an "E" or "e" inserted between the number and the exponent. The number of zeros following the scientific notation indicator determines the minimum number of digits to output for the exponent. The "E+" and "e+" formats indicate that a plus sign or minus sign should always precede the exponent. The "E", "E-", "e", or "e-" formats indicate that a sign character should precede only negative exponents.

The following example formats several numeric values using the specifiers for scientific notation.

let
    Source =
    {
        Number.ToText(86000, "0.###E+0", ""),
        // Displays 8.6E+4

        Number.ToText(86000, "0.###E+000", ""),
        // Displays 8.6E+004

        Number.ToText(86000, "0.###E-000", "")
        // Displays 8.6E004
    }
in
    Source

Back to table

Escape characters

The "#", "0", ".", ",", "%", and "‰" symbols in a format string are interpreted as format specifiers rather than as literal characters. Depending on their position in a custom format string, the uppercase and lowercase "E" as well as the + and - symbols can also be interpreted as format specifiers.

To prevent a character from being interpreted as a format specifier, you can:

• Precede it with a backslash.
• Surround it with a single quote.
• Surround it with two double quotes.

Each of these characters acts as escape characters. The escape character signifies that the following character is a character literal that should be included in the result string unchanged.

To include a backslash in a result string, you must escape it with another backslash (\\).

To include a single quote in a result string, you must escape it with a backslash (\'). If another single quote that isn't escaped precedes the escaped single quote, the backslash is displayed instead ('\' displays \).

To include a double quote in a result string, you must escape two of them with a backslash (\"").

The following example uses escape characters to prevent the formatting operation from interpreting the "#", "0", and "" characters as either escape characters or format specifiers.

let
    Source =
    {
        Number.ToText(123, "\#\#\# ##0 dollars and \0\0 cents \#\#\#"),
        // Displays ### 123 dollars and 00 cents ###

        Number.ToText(123, "'###' ##0 dollars and '00' cents '###'"),
        // Displays ### 123 dollars and 00 cents ###

        Number.ToText(123, """###"" ##0 dollars and ""00"" cents ""###"""),
        // Displays ### 123 dollars and 00 cents ###

        Number.ToText(123, "\\\\\\ ##0 dollars and \0\0 cents \\\\\\"),
        // Displays \\\ 123 dollars and 00 cents \\\

        Number.ToText(123, "'\\\' ##0 dollars and '00' cents '\\\'"),
        // Displays \\\ 123 dollars and 00 cents \\\

        Number.ToText(123, """\\\"" ##0 dollars and ""00"" cents ""\\\""")
        // Displays \\\ 123 dollars and 00 cents \\\
    }
in
    Source

Back to table

The ";" section separator

The semicolon (;) is a conditional format specifier that applies different formatting to a number depending on whether its value is positive, negative, or zero. To produce this behavior, a custom format string can contain up to three sections separated by semicolons. These sections are described in the following table.

Section separators ignore any preexisting formatting associated with a number when the final value is formatted. For example, negative values are always displayed without a minus sign when section separators are used. If you want the final formatted value to have a minus sign, you should explicitly include the minus sign as part of the custom format specifier.

The following example uses the ";" format specifier to format positive, negative, and zero numbers differently.

let
    Source =
    {
        Number.ToText(1234, "##;(##)"),
        // Displays 1234

        Number.ToText(-1234, "##;(##)"),
        // Displays (1234)

        Number.ToText(0, "##;(##);**Zero**")
        // Displays **Zero**
    }
in
    Source

Back to table

Character literals

Format specifiers that appear in a custom numeric format string are always interpreted as formatting characters and never as literal characters. This includes the following characters:

• 0
• #
• %
• ‰
• '
• \
• ""
• .
• ,
• E or e, depending on its position in the format string.

All other characters are always interpreted as character literals and, in a formatting operation, are included in the result string unchanged. In a parsing operation, they must match the characters in the input string exactly; the comparison is case-sensitive.

The following example illustrates one common use of literal character units (in this case, thousands):

let
    Source = Number.ToText(123.8, "#,##0.0K")
    // Displays 123.8K
in
    Source

There are two ways to indicate that characters are to be interpreted as literal characters and not as formatting characters, so that they can be included in a result string or successfully parsed in an input string:

• By escaping a formatting character. For more information, go to Escape characters.

• By enclosing the entire literal string in quotation apostrophes.

The following example uses both approaches to include reserved characters in a custom numeric format string.

let
    Source =
    {
        Number.ToText(9.3, "##.0\%"),
        // Displays 9.3%

        Number.ToText(9.3, "\'##\'"),
        // Displays '9'

        Number.ToText(9.3, "\\##\\"),
        // Displays \9\

        Number.ToText(9.3, "##.0'%'"),
        // Displays 9.3%

        Number.ToText(9.3, "'\'##'\'"),
        // Displays \9\

        Number.ToText(9.3, "##.0""%"""),
        // Displays 9.3%

        Number.ToText(9.3, "\""##\""")
        // Displays "9"
    }
in
    Source

Notes

Floating-Point infinities and NaN

Regardless of the format string, if the value of a Decimal.Type, Single.Type or Double.Type floating-point type is positive infinity, negative infinity, or not a number (NaN), the formatted string is the value of the respective Number.PositiveInfinity, Number.NegativeInfinity, or Number.NaN constants specified by the currently applicable culture.

Rounding and fixed-point format strings

For fixed-point format strings (that is, format strings that don't contain scientific notation format characters), numbers are rounded to as many decimal places as there are digit placeholders to the right of the decimal point. If the format string doesn't contain a decimal point, the number is rounded to the nearest integer. If the number has more digits than there are digit placeholders to the left of the decimal point, the extra digits are copied to the result string immediately before the first digit placeholder.

Back to table

Example

The following example demonstrates two custom numeric format strings. In both cases, the digit placeholder (#) displays the numeric data, and all other characters are copied to the result string.

let
    Source =
    {
        Number.ToText(1234567890, "(###) ###-####"),
        // Displays (123) 456-7890

        Number.ToText(42, "My Number = #")
        // Displays My number = 42
    }
in
    Source

Back to table

Related content

• How culture affects text formatting
• Number type conversion
• Data Types in Power Query
• Standard Numeric Format Strings