FormatNumber()

Formats a number as a numeric type, such as a decimal, date, or currency value.

You can also use this function to convert numbers stored in strings to a number data type, and to round numbers to a certain number of decimal places.

The FormatNumber() function has three parameters:

  • number (string or number): Required. The number that you want to format. This function assumes that the input number uses a period (.) as a decimal separator.

  • formatType (string): Required. The number type to convert the number to. Accepted values:

    • C - Formats the number as a currency value.
    • D - Formats the number as a decimal number.
    • E - Formats the number using scientific notation.
    • F - Formats the number to a fixed number of decimal places (two decimal places by default).
    • G - Formats the number without thousands separators.
    • N - Formats the number with thousands separators.
    • P - Formats the number as a percentage.
    • R - Round-trip (format ensures value parsed to string can be parsed back to numeric value)
    • X - Formats the number as a hexadecimal value.

    You can optionally follow this code with a number to indicate the precision of the number. For example, a currency value with two decimal places uses the parameter C2.

  • cultureCode (string): A POSIX locale code, such as en_US or zh-TW. When you provide this value, the resulting number is formatted using patterns that suit the specified locale.

To use the function, pass it a number to format, and the numeric type that you want to convert it to. You can optionally include a POSIX locale code. If you do, the function returns the resulting number in a format that aligns with the formatting practices of the specified culture.

The following sections show several of the ways that you can use this function to format numbers.

This example uses the N (number) type. The N is followed by a 2, indicating that the resulting number is rounded to two decimal places.

The function returns 123.45.

This example converts a string to a number data type.

The function returns 1,234.56.

You can use the G (general) number type to remove the thousands separators from numbers that are passed as strings.

The function returns 1234.56.

This example formats the input number as a currency value, rounded to two decimal places. It also formats the resulting number using rules that align with the de_DE (Germany) locale.

The function returns 123,45 €.

This example converts the result of dividing two numbers into a percentage.

The function returns 12.883%.