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
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 half-even 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 “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% |
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 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.
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
| Version | Status | Notes |
| 10.0 | Updated | Carried over from Panorama 6.0, but added engineering notation. |