Search

Module:Editor

Version:2.2.1 +

Type:String

Category:Conversion

 

Description

The Formula FormatNumber converts a real number to a string with a specified text format.

Parameters

Number:

This is the number of type long or double that is to be converted.

Format:

This is the desired text format.

Culture (23.11+):

This is the desired culture in which the number is presented. See also 'Formats and cultures' below. If this parameter is empty, the culture from the configuration will be used.

Example

Description

In the following table it is shown how the number 1000 will be represented using a number of formats.

Format Output
0 1000
0.0 1000.0
0.00 1000.00
#,##0 1,000
#,##0.0 1,000.0
#,##0.00 1,000.00

 

Formats and cultures

The actual way in which a number will be presented depends on the chosen format, but also on the chosen Regional Setting Culture. The culture can be set in the Configuration Format. In the example above the culture 'en-US' is used. If the culture is, for instance, 'nl-NL' then the format will remain the same, but the representation of the number will follow the dutch number notation. This means that the decimal mark will be a ',' and the thousands separator will be a '.'. From version 23.11 it is also possible to set the culture in a parameter of the formula. This way the culture can be set on a formula level instead of an application level, to ensure bilingual usage.

Difference between # and 0

The difference between using '#' and '0' is that on the position of # a number will appear only if it actually exists. Suppose we want to determine the format of the number 10. If we would use the format ##0.0 then the output will be 10.0. On the position of the first '#' is no text and will therefore not be shown. If we would use the format 000.0 then the output will be 010.0. On the position of the first '0' is no text but a 0 will be shown nonetheless.

Format of positive and negative numbers

So far we have assumed that the format of positive and negative numbers is to be the same. It is also possible to explicitly state the format of both sets of numbers. Suppose we would like to denote positive numbers with one decimal whereas negative numbers should be denoted with two decimals. This can be accomplished using the following notation: #,##0.0;-#,##0.00.

Furthermore, if we would like to add a separate notation for 0 then this can also be included in the notation: #,##0.0;-#,##0.00; zero. In this case 0 will be converted to the text 'zero'.

Related Topics

FormatDate: To convert dates to text

External Links

http://msdn2.microsoft.com/en-us/library/0c899ak8.aspx: Further explanation on the text format.

Updated: 2013-03-25