Number
The number to format.
DecimalPlaces
The number of decimal places to use. May be negative, in which case the number is rounded to the left of the decimal point. If omitted, 2 is assumed.
SkipGrouping
FALSE to include thousands separators and TRUE to exclude them. If omitted, FALSE 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 number.
Returns a number as text with a specified format. This function takes the configured locale into account. FIXED (42.12) FIXED (42,12) returns "42.12" if that locale is US English and "42,12" if that locale is French.
Decimal places
Use the second parameter to set the number of decimal places the returned number is formatted with. FIXED (1234.56789, 3) FIXED (1234,56789; 3) returns "1,234.568" (rounded to three decimal places) if the configured locale is US English. FIXED (1234.56789, 3, FALSE) FIXED (1234,56789; 3; FALSE) returns "1234.568", without the thousands separator, because of the third parameter, FALSE.
Rounding to the left of the decimal separator
The second parameter can be set to a negative number, in which case the number is rounded to the left of the decimal separator. FIXED (1234.56789, 0) FIXED (1234,56789; 0) returns "1,235", FIXED (1234.56789, - 1) FIXED (1234,56789; - 1) returns "1,230", FIXED (1234.56789, - 2) FIXED (1234,56789; - 2) returns "1,200" and FIXED (1234.56789, - 3) FIXED (1234,56789; - 3) returns "1,000".
Related functions
- Use FORMAT.CURRENCY to format monetary values (-0.75 can be formatted as "($0.75)").
- 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 FORMAT to access features not offered by FIXED. FORMAT can pad the returned number with leading zeroes ("00123") and offers more flexibility in terms of specifying the number of decimal places a number should be formatted with. However, only FIXED can round a number to the left of the decimal separator.
Examples
Returns "42.12" if the configured locale is US English and "42,12" if the configured locale is French.
Returns "42,12".
Returns "1,234.568" if the configured locale is US English. The second parameter, 3, specifies that numbers should be formatted with three decimal places.
Returns "1234.568", without the thousands separator, because the third parameter is set to TRUE.
Returns "1,230". When the second parameter is set to a negative value, the number is rounded to the left of the decimal separator.