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, 26-54

Basics

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

Rounding

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).

Leading Zeros

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.)

Comma Separators

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

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)

Scientific Notation

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.)

Engineering Notation

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

Prefixes and Suffixes

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 “as-is” 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%

Plurals

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.

Spelling Numbers as Words

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 option–6 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 option–4 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.

Multiple Component Numbers

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 219-36-8847
76543 000-07-6543
1209876.543 001-20-9877

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

VersionStatusNotes
10.0UpdatedCarried over from Panorama 6.0, but added engineering notation.