Numeric patterns allow you to control how a number is displayed or converted to text. All of the numbers listed below have the value 2654, but have been converted to text using different patterns:
2654, 2,654, $2,654.00, 002654, 2.654e+3, 2654
The basic building block of any numeric pattern is the # symbol. This symbol represents one or more digits of the number being converted. By arranging one or more # symbols with other characters and punctuation you can control the format of the number being displayed.
The example below shows a very basic format for converting numbers to text. This format formats the number with two digits after the decimal point–no matter how many digits actually exist in the original number.
Number 
pattern(Number,"#.##") 


4 
☞  4.00 
6.5 
☞  6.50 
983.12 
☞  983.12 
0.6291 
☞  0.63 
If the original number has too many digits after the decimal point (as does the number on the last line of the table above) the number will be rounded (not truncated) to fit into the formatted text (as the example shows). Rounding is done using what is called the halfeven rounding or round half to even method. The Wikipedia Rounding article covers the details of this method of rounding.
If the pattern has extra # symbols in front of the decimal point the pattern( function will add leading zeros in front of the number.
Number 
pattern(Number,"#####.#") 


4 
☞  00004.0 
6.5 
☞  00006.5 
983.12 
☞  00983.1 
8911272 
☞  8911272.0 
As the last number shows, the pattern( function will never chop off digits in front of the decimal point. Even though 8911272 has 7 digits and the pattern only has 5 # symbols in front of the decimal point, all 7 digits are included in the converted value. The # symbols in front of the decimal point define a lower limit on the number of digits in front of the decimal point, not an upper limit. (If you want to establish an upper limit you could use a text funnel to strip off the extra characters.)
Fixed point numbers often have a comma every third digit to make the number easier to read. Place a comma anywhere within or adjacent to the stream of # symbols if you want the number to be formatted with a comma. This example shows numbers with a comma and with no decimal point or digits after the decimal point.
Number 
pattern(Number,"#,") 


782 
☞  782 
298112 
☞  298,112 
67123329 
☞  67,123,329 
Negative numbers are usually converted with a minus sign in front of the number. If you want a minus sign at the end of the number put a minus sign after the last # in the pattern.
Number 
pattern(Number,"#,.##") 


4981.12 
☞  4,981.12 
323.89 
☞  323.89 
Negative numbers can also be displayed with parentheses around them. Simply put ( and ) characters around the # symbols. In this case, the pattern( function will put an extra space after the number if the number is positive. The extra space helps make positive numbers line up with negative numbers if they are displayed in a list.
Number 
pattern(Number,"(#,.##)") 


4981.12 
☞  4,981.12 
323.89 
☞  (323.89) 
To output a number using scientific notation, add an E (or e) after the last # symbol.
Number 
pattern(Number,"#.####e") 


7542 
☞  7.5420e+3 
75421 
☞  7.5421e+4 
754218 
☞  7.5422e+5 
Note: Scientific notation does not support commas every three digits.)
To output a number using engineering notation, add an E (or e) after the last # symbol, and put three # symbols in front of the decimal point. When engineering notation is used, the exponent will always be a multiple of 3.
Number 
pattern(Number,"###.####e") 


7542 
☞  7.5420e+3 
75421 
☞  75.421e+3 
754218 
☞  754.22e+3 
The previous section described basic numeric patterns. You can embellish on these patterns by adding a prefix and or suffix. You may add any characters you want in the prefix or suffix, and they will be added “asis” to the final converted number. The most common prefix is $ for monetary values.
Number 
pattern(Number,"$#,.##") 


98212 
☞  $98,212.00 
344.29 
☞  $344.29 
8374940.8823 
☞  $8,374,940.88 
Here is another example that adds a suffix for percentages.
Number 
pattern(Number,"#.##%") 


37 
☞  37.00% 
99.87 
☞  99.87% 
1.25 
☞  1.25% 
If a suffix contains a measurement unit, you may want to properly pluralize the units depending on the value being displayed. The pattern can use the ~ symbol to include an optional s in the suffix. The s is included if the value is not 1, for example 4 miles vs. 1 mile. Here is an example that converts text as kilograms.
Number 
pattern(Number,"#, kilogram~") 


1 
☞  1 kilogram 
50 
☞  50 kilograms 
1000 
☞  1,000 kilograms 
The ~ symbol can be used with any word that is plural with an s: mile~, ounce~, meter~, dollar~, cent~, hour~, day~, month~, year~, ohm~, volt~ etc. It does not work with words that change spelling when plural: foot (feet), inch (inches), etc.
Numbers are normally converted to text as a sequence of digits. Through the use of a special symbol (§), a number can be spelled out as words (for example one hundred twenty three). There should only be one § symbol in the pattern. Press option6 to create the § symbol.
Only the integer part of the number is converted by the § symbol. If you are converting money you’ll probably want to convert the fractional part (cents) also. You can convert the fractional part with the ¢ symbol. Use one ¢ symbol for each digit you want to display (usually 2). Press option4 to create the ¢ symbol.
The formula below shows a typical pattern for spelling out dollar values:
Number 
pattern(Number,"§ dollar~ and ¢¢ cent~") 


1.67 
☞  1 dollar and 67 cents 
36.25 
☞  Thirty six dollars and 25 cents 
312.50 
☞  Three hundred twelve dollars and 50 cents 
If you want to spell out the cents also you must use two pattern functions like this:
Number 
pattern(Number,"§ dollar~ and ")+lower(pattern(Number*100,"§ cent~")) 


1.67 
☞  1 dollar and sixty seven cents 
36.25 
☞  Thirty six dollars and twenty five cents 
312.50 
☞  Three hundred twelve dollars and fifty cents 
The § symbol normally converts the number with the first letter capitalized and the rest lower case. To change this you can use the lower(, upper(, or upperword(functions.
Numbers are normally converted to text as a continuous sequence of digits. You can also convert a number with the digits split up by punctuation or other characters. To do this, create a pattern with # symbols broken up by other characters. For example, here is a pattern that converts a number into standard social security number format (000–00–0000).
Number 
pattern(Number,"#########") 


219368847 
☞  219368847 
76543 
☞  000076543 
1209876.543 
☞  001209877 
As the last example shows, the number is rounded to the nearest integer before conversion when the pattern has multiple components. Here’s another example that converts a number into the standard format for a combination lock.
Number 
pattern(Number,"Right ## Left ## Right ##") 


219368 
☞  Right 21 Left 93 Right 68 
76543 
☞  Right 07 Left 65 Right 43 
1209876543 
☞  Right 12 Left 09 Right 87 
Up to 15 digits can be displayed when a pattern is used.
See Also
History
Version  Status  Notes 
10.0  Updated  Carried over from Panorama 6.0, but added engineering notation. 