The function FORMAT_CURRENCY converts a currency into a string given an optional format string, and locale.
This 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]
- If the first character is a dollar sign, the currency will be formatted with a leading currency symbol.
- If there is a zero followed by a comma, the currency will be grouped by thousands.
- There must be a zero
- 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.