FORMAT_CURRENCY

Chandra Bergmann

The FORMAT_CURRENCY function takes a Currency and converts it into a string given an optional format string, and locale.

The format string specifies: whether to include thousandths separators, how many decimal places, and whether to include a symbol for the currency (such as a dollar sign: $). For example, the format string "$0,0.00" will include the currency symbol, group by thousandths, and display two decimal places, it would format one U.S. dollar as "$1.00" The locale is used as in the FORMAT_NUMBER function to determine which characters to use to group thousandths, and as the decimal point. In FORMAT_CURRENCY, it is also used to determine which symbol to display to disambiguate currencies. For example, with a locale of "en-US", and a currency of "USD", the dollar sign will be chosen. However, in the "en-CA" locale, the symbol will be "US$" to disambiguate between Canadian dollars.

Declaration

FORMAT_CURRENCY(currency, format_string, locale_string) -> formatted_string

Parameters

currency (required, type: currency)
The currency to be formatted.
format_string (optional, type: string, default: "$0,0.00") A string that configures how the currency is formatted. locale_string (optional, type: string, default: "en-US") The locale used to choose the currency symbol. Locale must match a valid LCID String. Return Values formatted_string (type: string) A string containing the formatted currency as specified by the format_string. Examples For the following examples assume that we have a variable named us_pennies that holds a Currency that represents 101 U.S. pennies. Note that this is a different value from 1.01 dollars, as a Currency carries both an amount and a precision. See Working with Numbers and Currency in Airscript for more details. us_pennies = CURRENCY(101, "USD", 2) If we do not provide any of the optional arguments, the formatted currency will begin with a dollar sign (assuming a user locale of "en-US"), be grouped by thousandths, and have two decimal places. FORMAT_CURRENCY(us_pennies) -> "$1.01"

This is the same as if we had called FORMAT_CURRENCY with the format string "$0,0.00" FORMAT_CURRENCY(us_pennies, "$0,0.00") -> "$1.01" However, if we force the locale to be "en-CA" the currency symbol will instead be "US$"

FORMAT_CURRENCY(us_pennies, "$0,0.00", "en-CA") -> "US$1.01"

Additional trailing zeros will increase the number of decimal places. For each zero after the period there will be an additional decimal place displayed in the final result.

FORMAT_CURRENCY(us_pennies, "$0,0.0000") -> "$1.0100"

Discussion

The format string specification is as follows where terms surrounded by brackets, [ and ], are optional. Note that the format string must contain at least a zero, all other terms are optional.

[\$] [0,] 0 [.precision]
1. If the first character is a dollar sign, the currency will be formatted with a leading currency symbol.
2. If there is a zero followed by a comma, the currency will be grouped by thousands.
3. There must be a zero
4. If there is a period followed by a number of zeros, the precision used will match the number of zeros provided. For example .000 means that three decimal places should be displayed.