The FORMAT_CURRENCY function converts 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: The user's current locale)
The locale used to choose the currency symbol.

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.

Was this article helpful?

0 out of 0 found this helpful

Add comment

Please sign in to leave a comment.