Number
The number to format.
Currency
Either an ISO 4217 currency code, such as "USD", "EUR", "GBP" or "JPY", or a member of the Currency enum (such as Currency.UsdCurrency,Usd, Currency.EurCurrency,Eur, Currency.GbpCurrency,Gbp or Currency.JpyCurrency,Jpy). The list of supported Currency values is available above. Using the enum is recommended, as typos can be caught faster. If omitted, Currency.UsdCurrency,Usd is assumed.
DecimalPlaces
The number of decimal places a number should be formatted with. Must be greater than or equal to 0. If omitted, the default for the currency is assumed (typically, 2).
SkipGrouping
FALSE to include thousands separators and TRUE to exclude them. If omitted, FALSE is assumed.
RoundingMode
The mode to use when rounding numbers. These are the supported values:
| Formula | Description |
|---|---|
| RoundingMode.CeilingRoundingMode,Ceiling | Rounds towards positive infinity. For example, 2.1 becomes 3 and -2.1 becomes -2. |
| RoundingMode.FloorRoundingMode,Floor | Rounds towards negative infinity. For example, 2.9 becomes 2 and -2.9 becomes -3. |
| RoundingMode.ExpandRoundingMode,Expand | Rounds away from zero. For example, 2.1 becomes 3 and -2.1 becomes -3. |
| RoundingMode.TruncateRoundingMode,Truncate | Rounds towards zero. For example, 2.9 becomes 2 and -2.9 becomes -2. |
| RoundingMode.HalfCeilingRoundingMode,HalfCeiling | Rounds to the nearest neighbor, rounding towards positive infinity on ties. For intance, 2.5 becomes 3 and -2.5 becomes -2. |
| RoundingMode.HalfFloorRoundingMode,HalfFloor | Rounds to the nearest neighbor, rounding towards negative infinity on ties. For example, 2.5 becomes 2 and -2.5 becomes -3. |
| RoundingMode.HalfExpandRoundingMode,HalfExpand (default) | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds away from zero. For example, 2.5 becomes 3 and -2.5 becomes -3. |
| RoundingMode.HalfTruncateRoundingMode,HalfTruncate | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds towards zero. For example, 2.5 becomes 2 and -2.5 becomes -2. |
| RoundingMode.HalfEvenRoundingMode,HalfEven | Rounds to the nearest neighbor, rounding to the nearest even number on ties. This mode is known as Banker's Rounding because it is, on average, free of bias. For example, 2.5 becomes 2 and 3.5 becomes 4. |
Display
CurrencyDisplay.CodeCurrencyDisplay,Code, if the ISO 4217 currency code should be part of the formatted value (such as "4 USD"), or CurrencyDisplay.SymbolCurrencyDisplay,Symbol if the currency symbol should be part of the formatted value (such as "$4"). If omitted, CurrencyDisplay.SymbolCurrencyDisplay,Symbol is assumed.
Locale
The locale to use when doing the conversion.
A locale represents a language and possibly a region. If omitted, the configured locale is used.
Use a value such as Locale.EnglishLocale,English to provide a locale (the full list is below). If your chosen locale is not part of the list, use the LOCALE function instead.
All locales may not be available. If a locale is not available, a fallback locale can be used. To provide such fallback locales, use the LOCALE function.
The LOCALE function accepts so-called Unicode Locale Identifiers. Every value in the list below corresponds to such an identifier.
| Formula | Unicode Locale Identifier | Description |
|---|---|---|
| Locale.ArabicLocale,Arabic | ar | Arabic |
| Locale.BulgarianLocale,Bulgarian | bg | Bulgarian |
| Locale.ChineseCnLocale,ChineseCn | zh-CN | Chinese (simplified), as used in mainland China |
| Locale.ChineseTwLocale,ChineseTw | zh-TW | Chinese (traditional), as used in Taiwan |
| Locale.CroatianLocale,Croatian | hr | Croatian |
| Locale.CzechLocale,Czech | cs | Czech |
| Locale.DanishLocale,Danish | da | Danish |
| Locale.DutchLocale,Dutch | nl | Dutch |
| Locale.DutchBeLocale,DutchBe | nl-BE | Dutch, as used in Belgium |
| Locale.EnglishLocale,English | en | English |
| Locale.EnglishAuLocale,EnglishAu | en-AU | English, as used in Australia |
| Locale.EnglishCaLocale,EnglishCa | en-CA | English, as used in Canada |
| Locale.EnglishInLocale,EnglishIn | en-IN | English, as used in India |
| Locale.EnglishNzLocale,EnglishNz | en-NZ | English, as used in New Zealand |
| Locale.EnglishUkLocale,EnglishUk | en-GB | English, as used in the United Kingdom |
| Locale.EnglishUsLocale,EnglishUs | en-US | English, as used in the United States of America |
| Locale.EnglishZaLocale,EnglishZa | en-ZA | English, as used in South Africa |
| Locale.FinnishLocale,Finnish | fi | Finnish |
| Locale.FrenchLocale,French | fr | French |
| Locale.FrenchBeLocale,FrenchBe | fr-BE | French, as used in Belgium |
| Locale.FrenchCaLocale,FrenchCa | fr-CA | French, as used in Canada |
| Locale.FrenchChLocale,FrenchCh | fr-CH | French, as used in Switzerland |
| Locale.GermanLocale,German | de | German |
| Locale.GermanAtLocale,GermanAt | de-AT | German, as used in Austria |
| Locale.GermanChLocale,GermanCh | de-CH | German, as used in Switzerland |
| Locale.GreekLocale,Greek | el | Greek |
| Locale.HebrewLocale,Hebrew | he | Hebrew |
| Locale.HindiLocale,Hindi | hi | Hindi |
| Locale.HungarianLocale,Hungarian | hu | Hungarian |
| Locale.IndonesianLocale,Indonesian | id | Indonesian |
| Locale.ItalianLocale,Italian | it | Italian |
| Locale.ItalianChLocale,ItalianCh | it-CH | Italian, as used in Switzerland |
| Locale.JapaneseLocale,Japanese | ja | Japanese |
| Locale.KoreanLocale,Korean | ko | Korean |
| Locale.NorwegianLocale,Norwegian | nb | Norwegian (bokmål) |
| Locale.PersianLocale,Persian | fa | Persian |
| Locale.PolishLocale,Polish | pl | Polish |
| Locale.PortugueseLocale,Portuguese | pt | Portuguese |
| Locale.PortugueseBrLocale,PortugueseBr | pt-BR | Portuguese, as used in Brazil |
| Locale.RomanianLocale,Romanian | ro | Romanian |
| Locale.RussianLocale,Russian | ru | Russian |
| Locale.SlovakLocale,Slovak | sk | Slovak |
| Locale.SpanishLocale,Spanish | es | Spanish |
| Locale.SpanishArLocale,SpanishAr | es-AR | Spanish, as used in Argentina |
| Locale.SpanishEsLocale,SpanishEs | es-ES | Spanish, as used in Spain |
| Locale.SpanishMxLocale,SpanishMx | es-MX | Spanish, as used in Mexico |
| Locale.SwedishLocale,Swedish | sv | Swedish |
| Locale.ThaiLocale,Thai | th | Thai |
| Locale.TurkishLocale,Turkish | tr | Turkish |
| Locale.UkrainianLocale,Ukrainian | uk | Ukrainian |
| Locale.VietnameseLocale,Vietnamese | vi | Vietnamese |
Returns
A formatted textual representation of a monetary value.
Returns a monetary value as text. FORMAT.CURRENCY (35.5) FORMAT.CURRENCY (35,5) returns "$35.50" if the configured locale is US English and "35,50 $" if the configured locale is German.
By taking the configured locale into account, FORMAT is able to use decimal and thousands separators appropriate for the configured locale, as well as appropriate digits.
This function is not commonly supported by spreadsheets.
Supported currencies
This function supports all currencies that are in use today (through the ISO 4217 standard). A three-letter currency code, such as "USD" or "EUR", may be given as the second parameter. If no currency code is given, "USD" is assumed.
As a convenience, you can specify more than twenty currencies by writing Currency.Currency,, followed by the currency code. These currencies are supported:
| Formula | Name |
|---|---|
| Currency.ArsCurrency,Ars | Argentine Peso |
| Currency.AudCurrency,Aud | Australian Dollar |
| Currency.BrlCurrency,Brl | Brazilian Real |
| Currency.CadCurrency,Cad | Canadian Dollar |
| Currency.ChfCurrency,Chf | Swiss Franc |
| Currency.CnyCurrency,Cny | Chinese Yuan Renminbi |
| Currency.DkkCurrency,Dkk | Danish Krone |
| Currency.EurCurrency,Eur | Euro |
| Currency.GbpCurrency,Gbp | British Pound Sterling |
| Currency.HkdCurrency,Hkd | Hong Kong Dollar |
| Currency.InrCurrency,Inr | Indian Rupee |
| Currency.JpyCurrency,Jpy | Japanese Yen |
| Currency.MxnCurrency,Mxn | Mexican Peso |
| Currency.NokCurrency,Nok | Norwegian Krone |
| Currency.NzdCurrency,Nzd | New Zealand Dollar |
| Currency.PhpCurrency,Php | Philippine peso |
| Currency.PlnCurrency,Pln | Polish Zloty |
| Currency.SekCurrency,Sek | Swedish Krona |
| Currency.SgdCurrency,Sgd | Singapore Dollar |
| Currency.UsdCurrency,Usd | United States Dollar |
| Currency.ZarCurrency,Zar | South African Rand |
Decimal places
The third parameter is used to control the number of decimal places the number is formatted with. This formula returns "$1,004", using no decimals:
Thousands separators
The fourth parameter may be set to TRUE to prevent thousands separators from being used. This formula returns "$1024.82" (if the configured locale is US English), and not "$1,024.82":
Related functions
- Use FORMAT to format regular numbers.
- Use FORMAT.FRACTION to format fractions (0.75 can be formatted as "3/4").
- Use FORMAT.PERCENTAGE to format percentages (0.75 can be formatted as "75%").
- Use FORMAT.SCIENTIFIC to use scientific notation (15,000 can be formatted as "1.5E4").
- Use FORMAT.ENGINEERING to use engineering notation (15,000 can be formatted as "15E3").
- Use FIXED to round a number to the left of the decimal separator (7,523 can be formatted as "7,500").
Examples
Returns "$1,003.75" if the configured locale is US English and "1.003,75 $" if the configured locale is German.
Returns "1.003,75 $".
Returns "1.003,75 €".
Returns the array { "$1,003.75", "$0.21" }{ "$1,003.75"; "$0.21" } if the configured locale is US English. When invoked with an array, FORMAT.CURRENCY is invoked once per array element and the results are collected in an array.
Returns "$1,004" if the configured locale is US English, using no decimals.
Returns "$1024.82" (if the configured locale is US English), and not "$1,024.82". Thousands separators are not used.
Returns "USD 4.00" (if the configured locale is US English), using an ISO 4217 currency code instead of a currency symbol.