Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

FormatNumber ( number, format )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.0  (58 votes)
  Discuss this Custom Function

Lewis C. Lorenz, Lorenz Companies
www.LorenzCom.com/software

Non-recursive function returns a text string of the 'number' in the requested 'format'.

Sample Input:
FormatNumber ( 14725.3675, "$,.2 US" )
FormatNumber ( -14725.3675, "$(,.2) US\r0\" )
FormatNumber ( -14725.3675, "#,.\c255\" )
FormatNumber ( 1, "Yes||No" )
FormatNumber ( -59703600 , \".-3-\" )
FormatNumber ( 5973600, "^" )
FormatNumber ( 59703600, "~0 kg" )
Sample Output:
$14,725.37 US
$(14,725.36) US [ rounding off ]
#-14,725.3675 [ text color is red ]
Yes
59704000-
5.9736e6
59.7036e6 kg


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Description:

FormatNumber ( number, format )

Returns a text string of the 'number' in the requested 'format'.

By Lewis C. Lorenz
Updated: 4/12/18

Non-recursive function allows complete FileMaker Pro number formatting using an intuitive, liberal format structure. Free-form formatting provides the user flexible control over what is included in the final formatted result. Escaped options can be used instead. Include the following elements in the format string in the order the user desires to produce a formatted number.

HELP:
Passing '?' as the only content of 'format' returns a shortened version of this text.

NUMBER FORMATTING:
, - (thousands separator) integer portion of the 'number' is thousands separated.
. - (decimal separator) decimal portion of the 'number' is included.
0…n - (integer) specifies the rounding and truncation precision applied to the 'number':
0 - returns integer without decimal and decimal separator. Also, acts as a separator to allow positioning of negative signs and notations.
1…n - rounding or truncation precision of the formatted 'number' (negative affects integer; positive affects decimal).
^ - (caret) formats 'number' in scientific E notation.
~ - (tilde) formats 'number' in engineering E notation.

NEGATIVE SIGN FORMATTING:
- - minus sign (default) (default: left).
<> - angle brackets (default: left and rigth).
() - parentheses (default: left and right).
CR - credit symbol (default: right).
∆ - delta symbol (default: left). (Dark delta is output.)
Minus sign, credit and delta symbols can be positioned on the left or right of the formatted 'number' by placing them to the left or right of an included decimal (.) or zero (0) separator in 'format'. Negative signs are placed at their default position when a decimal or zero is not included.

BOOLEAN FORMATTING:
|| - two pipe characters separate boolean values. True 'number' (non-zero/non-empty) returns left value; false 'number' (zero/empty) returns right value.

NOTATION FORMATTING:
% - percent sign. Inclusion in 'format' forces multiplication of the formatted 'number' by 100 (default: right).
$, £, #, etc. - dollar, pound, hash, and any other characters, can be included in the formatted 'number' (default: left).
Percent, currency and other notations can be positioned on the left or right of the formatted 'number' by placing them on the left or right of an included decimal (.) or zero (0) separator in 'format'. Notations are placed at their default position when a decimal or zero is not included.

ESCAPED OPTIONS FORMATTING:
\T - (text) thousands separator (default: system).
\D - (text) decimal separator (default: system).
\R - (boolean) round or truncate 'number' to passed precision integer (true: round on; false: truncate on) (default: true).
\PI - (integer) round or truncate to this postive or negative integer precision (default: empty).
\Z - (boolean) show 'number' if zero (default: true).
\F - (boolean) fill (pad) decimal with zeros to length of passed precision (default: true).
\C - (integers) comma-separated RGB number text (#,#,#) to color negative 'number' (default: black).
\X - (integer) exponentiation formatting: 0 = none, 1 = scientific, 2 = engineering (default: 0).
\SL - (text) negative sign (-, <>, (), CR, ∆) appearing on left side of 'number' (default: minus sign).
\SR - (text) negative sign (-, <>, (), CR, ∆) appearing on right side of 'number' (default: empty).
\PL - (boolean) proximity of negative sign on left side is next to 'number' (default: true).
\PR - (boolean) proximity of negative sign on right side is next to 'number' (default: true).
\NL - (text) notation sign ($, %, #, etc.) to appear on left side of 'number' (default: empty).
\NR - (text) notation sign ($, %, #, etc.) to appear on right side of 'number' (default: empty).
\BP - (text) positive response to boolean formatting.
\BN - (text) negative response to boolean formatting.
Escaped options override free-form 'format', defaults and add formatting. Enclose each option with escape (backslash) characters \\\\.

Examples:
FormatNumber ( 14725.3675, "$,.2 US" ) = $14,725.37 US
FormatNumber ( -14725.3675, "$(,.2) US\r0\" ) = $(14,725.36) US [ rounding off ]
FormatNumber ( -14725.3675, "#,.\c255\" ) = #-14,725.3675 [ text color is red ]
FormatNumber ( 1, "Yes||No" ) = Yes
FormatNumber ( -59703600 , \".-3-\" ) = 59704000-
FormatNumber ( 5973600, "^" ) = 5.9736e6
FormatNumber ( 59703600, "~0 kg" ) = 59.7036e6 kg

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

This is my Custom Function and I want to edit it

Discuss:

help me
i have problem :
LV1.listitems.Clear
Call NisBobot
For i = 1 To LV.listitems.Count
For j = 1 To 7
z = FormatNumber(LV.listitems(i).ListSubItems(j), 2)
nNormal = FormatNumber(z / FormatNumber(nMax(j), 2), 2)
Set lst1 = LV1.listitems.Add(, , "")
LV1.listitems(i).SubItems(j) = nNormal
Next j
Next i

this message error " type mismacth"
please solution,...thank

Juju, indonesia/bengkulu
November 04, 2015 9:16pm

The format should be string.
Change: FormatNumber(LV.listitems(i).ListSubItems(j), 2)
to: FormatNumber(LV.listitems(i).ListSubItems(j), "2")

Lewis Lorenz, Tulsa
November 22, 2016 1:27pm

Needs an option to round up or down, so if 1.80, should round to 2, currently rounds everything down to 1.

Daniel H., United States
April 05, 2018 10:48pm

Updated function 4/12/2018 to adjust rounding and truncation formatting:

NUMBER FORMATTING:
0…n - (integer) specifies the rounding and truncation precision applied to the 'number':
0 - returns integer without decimal and decimal separator. Also, acts as a separator to allow positioning of negative signs and notations.
1…n - rounding or truncation precision of the formatted 'number' (negative affects integer; positive affects decimal).
ESCAPED OPTIONS FORMATTING:
\R - (boolean) round or truncate 'number' to passed precision integer (true: round on; false: truncate on) (default: true).
\PI - (integer) round or truncate to this postive or negative integer precision (default: empty).

Lewis Lorenz, Tulsa
April 12, 2018 7:42am

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 9 + 1 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. SortList ( theList )
  (Sat, Jun 23, 8:45am)
2. HexUUIDToNum_cf (_uuid)
  (Sat, Jun 16, 1:55pm)
3. httpResponseCode ( responseHeaders )
  (Tue, Jun 12, 10:40pm)
4. FindDuplicateCharacters ( string )
  (Fri, Jun 08, 5:01pm)
5. FieldRepetitionLast ( field ; maxRepetition )
  (Wed, Jun 06, 6:05pm)
6. CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
  (Mon, May 28, 8:23am)
7. JSONArrayLength ( JSONArrayStr )
  (Fri, May 25, 7:46am)
8. interpolation (y1; y2; y3; x1; x2; x3; x1y1; x2y1; x1y2; x2y2; rnd)
  (Sat, May 19, 3:55am)

RSS Feed of Custom Functions