FORMATNUMBER function

FORMATNUMBER(Number, MinimumNumberOfDecimalPlaces?, MaximumNumberOfDecimalPlaces?, OmitThousandsSeparators?, MinimumNumberOfIntegerDigits?, UseAccountingStyle?, LeadingUnit?, TrailingUnit?) FORMATNUMBER(Number; MinimumNumberOfDecimalPlaces?; MaximumNumberOfDecimalPlaces?; OmitThousandsSeparators?; MinimumNumberOfIntegerDigits?; UseAccountingStyle?; LeadingUnit?; TrailingUnit?)

Number

Number or { Number }

The number to format.

MinimumNumberOfDecimalPlaces

Number or { Number } (optional)

The minimum number of decimal places a number should be formatted with. Must be greater than or equal to 0 and less than or equal to the maximum number of decimal places. If omitted, 2 is assumed.

MaximumNumberOfDecimalPlaces

Number or { Number } (optional)

The maximum number of decimal places a number should be formatted with. Must be greater than or equal to the minimum number of decimal places. If omitted, this parameter is set to the minimum number of decimal places.

OmitThousandsSeparators

Logical or { Logical } (optional)

FALSE to include thousands separators and TRUE to exclude them. If omitted, FALSE is assumed.

MinimumNumberOfIntegerDigits

Number or { Number } (optional)

The minimum number of digits the integer part should be formatted with. If this parameter is greater than the number of digits of the integer part, it is padded with zeroes, meaning that 123 formatted with a minimum of five integer digits is rendered as "00123." If omitted, one is assumed.

UseAccountingStyle

Logical or { Logical } (optional)

Whether negative numbers should be enclosed in parentheses instead of being preceded with a minus sign. If omitted, FALSE is assumed.

LeadingUnit

Text or { Text } (optional)

The leading unit that should precede the returned formatted number.

TrailingUnit

Text or { Text } (optional)

The trailing unit that should follow the returned formatted number.

Returns

Text or { Text }

A formatted textual representation of a number.

Returns a number as text. FORMATNUMBER(1003.75)FORMATNUMBER(1003,75), for instance, returns "1,003.75" if the language of the app is set to US English and "1 003,75" if the language is set to German.

FORMATNUMBER takes the language of the app into account when converting numbers. For instance, a decimal point is used when the language is set to US English and a decimal comma is used when the language is set to French.

This function is specific to Calcapp.

Decimal places

The second and third parameters are used to control the number of decimal places the number is formatted with. If only the second parameter is given, it specifies the exact number of decimal places that are used.

This formula returns "1,003.7500", using exactly four decimal places:

FORMATNUMBER(1003.75, 4)FORMATNUMBER(1003,75; 4)

If a third parameter is given, the second parameter is interpreted as the minimum number of decimal places that should be used and the third parameter is interpreted as the maximum number of decimal places that should be used. FORMATNUMBER(1003, 2, 6)FORMATNUMBER(1003; 2; 6) uses between 2 and 6 decimal places. As 1003 is an integer and no decimal places are required, "1,003.00" is returned (for US English). FORMATNUMBER(1003.123456789, 2, 6)FORMATNUMBER(1003,123456789; 2; 6), on the other hand, returns "1,003.123457", because a maximum of six decimal places may be used.

Thousands separators

The fourth parameter may be set to TRUE to prevent thousands separators from being used. FORMATNUMBER(1024.816, 2, 2, TRUE)FORMATNUMBER(1024,816; 2; 2; TRUE) returns "1024.82" (for an app set to US English), and not "1,024.82".

Padding the number with leading zeroes

The fifth parameter, minimumNumberOfIntegerDigits, may be used to set the minimum number of integer digits. In practice, this pads the number with leading zeroes, if necessary. FORMATNUMBER(3.75, 2, 2, TRUE, 4)FORMATNUMBER(3,75; 2; 2; TRUE; 4), returns "0003.75".

Accounting style

The sixth parameter, useAccountingStyle, comes into play when formatting negative numbers. In some languages, accounting style means that negative numbers—denoting a currency amount—are formatted using parentheses. For UK English, FORMATNUMBER(-23, 2, 2, TRUE, 1, TRUE)FORMATNUMBER(-23; 2; 2; TRUE; 1; TRUE) returns "(23.00)". This convention isn't used in German and many other languages, meaning that if the app is set to German, "-23,00" is returned.

Leading and trailing units

The remaining parameters, leadingUnit and trailingUnit, are used to specify units. "$" or "€" can be used as leading units, for instance, and " lbs", " kg" and the like can be used as trailing units. For a US English app, FORMATNUMBER(23, 2, 2, TRUE, 1, TRUE, "$")FORMATNUMBER(23; 2; 2; TRUE; 1; TRUE; "$") returns "$23.00" and FORMATNUMBER(-23, 2, 2, TRUE, 1, TRUE, "$")FORMATNUMBER(-23; 2; 2; TRUE; 1; TRUE; "$") returns "($23.00)".

Related functions

Examples

FORMATNUMBER(Field1)FORMATNUMBER(Field1)

Returns a formatted version of the value of Field1, using the default settings of FORMATNUMBER (two decimal places are used as well as thousands separators).

Returns a formatted version of the value of Field1, using the formatting settings of Field1.

FORMATNUMBER(1003.75)FORMATNUMBER(1003,75)

Returns "1,003.75" if the language of the app is set to US English and "1 003,75" if the language is set to German.

FORMATNUMBER({ 1003.75, 0.213 }, 2)FORMATNUMBER({ 1003,75; 0,213 }; 2)

Returns the array { "1,003.75", "0.21" }{ "1,003.75"; "0.21" } if the language of the app is set to US English. When invoked with an array, FORMATNUMBER is invoked once per array element and the results are collected in an array.

FORMATNUMBER(1003.75, 4)FORMATNUMBER(1003,75; 4)

Returns "1,003.7500" if the language of the app is set to US English, using exactly four decimal places.

FORMATNUMBER(1003, 2, 6)FORMATNUMBER(1003; 2; 6)

Returns "1,003.00" if the language of the app is set to US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.

FORMATNUMBER(1003.123456789, 2, 6)FORMATNUMBER(1003,123456789; 2; 6)

Returns "1,003.123457" if the language of the app is set to US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.

FORMATNUMBER(1024.816, 2, 2, TRUE)FORMATNUMBER(1024,816; 2; 2; TRUE)

Returns "1024.82" (for an app set to US English), and not "1,024.82". The fourth parameter prevents thousands separators from being used.

FORMATNUMBER(1003.75, 2, 2, TRUE, 4)FORMATNUMBER(1003,75; 2; 2; TRUE; 4)

Returns "0003.75". The fifth parameter, 4, specifies the minimum number of integer digits (before the decimal separator). This pads the number with leading zeroes, if necessary.

FORMATNUMBER(-23, 2, 2, TRUE, 1, TRUE)FORMATNUMBER(-23; 2; 2; TRUE; 1; TRUE)

Returns "(23.00)", with the app language set to UK English. The sixth parameter, TRUE, specifies that "accounting style" should be used for negative numbers. For some locales, this has the effect of enclosing the number in parentheses instead of using a leading minus sign.

FORMATNUMBER(-23, 2, 2, TRUE, 1, TRUE, "$")FORMATNUMBER(-23; 2; 2; TRUE; 1; TRUE; "$")

Returns "($23.00)", with the app language set to UK English. Had the number been 23, "$23.00" would have been returned. The last parameter, "$", specifies that the leading unit should be "$".

FORMATNUMBER(23, 0, 2, TRUE, 1, FALSE, "", " lbs")FORMATNUMBER(23; 0; 2; TRUE; 1; FALSE; ""; " lbs")

Returns "23 lbs", with the app language set to UK English. The last parameter, "lbs", specifies that the trailing unit should be " lbs".