Software Venture Consulting

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

Free Web Tools
Free FileMaker Tools

Personal Pages

Shopping Cart
Shopping Cart


Free Newsletter


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:  

  Average rating: 4.0  (57 votes)
  Discuss this Custom Function

Lewis C. Lorenz, Lorenz Companies

Returns text string of number in the requested format. Function allows complete FileMaker Pro number formatting using an intuitive free-form format structure.

Sample Input:
FormatNumber ( 14725.3675, “$,.2 US” )
FormatNumber ( -14725.3675, “$(,.2) US\r0\” )
FormatNumber ( -14725.3675, “#,.\c255\” )
FormatNumber ( 1, “Yes||No” )
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]
59.7036e6 kg

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

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed


FormatNumber ( number, format )

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

By Lewis C. Lorenz
Last updated: 2/11/2014

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.

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

, - (thousands separator) integer portion of the number is "thousands separated".
. - (decimal separator) displays of the decimal portion of the number.
# - (number) specifies the rounding precision (controls the count of decimal number digits to display):
0 - number decimal and decimal separator is not displayed. Enables positioning of negative signs and notations.
1...n - Truncation and rounding precision of the number decimal.
. - decimal separator only, with no number, displays the unaltered number decimal.
^ - (caret) formats number in scientific E notation.
~ - (tilde) formats number in engineering E notation.

- - 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). (Black delta is output.)
Minus sign, credit and delta symbols can be displayed on the left or right of the formatted number by placing them to the left or right of an included decimal separator, or zero, in 'format'. Negative signs are placed at their default location when no decimal separator is included.

|| - two pipe characters divide boolean results. True number returns left side. False number returns right side.

% - percent sign. Inclusion in 'format' forces multiplication of the 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 placed on either side of the formatted number depending on whether you place them at the left or right of an included decimal separator, or zero, in 'format'. Notations are placed at their default position when no decimal separator is included. The user must insure that the notations in 'format' produces the desired formatted result.

\T - (text) thousands separator (default: system).
\D - (text) decimal separator (default: system).
\R - (boolean) round number to passed precision (default: true).
\Z - (boolean) show number if zero (default: true).
\F - (boolean) fill (pad) decimal with zeros to length of passed precision (default: true).
\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).
\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).
\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 \\\\.

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 ( 5973600, "^" ) = 5.9736e6
FormatNumber ( 59703600, "~0 kg" ) = 59.7036e6 kg

Revised February 11, 2014. Added code to process numbers input in scientific notation format and to return numbers in scientific and engineering notation format. Reformatted the boolean separator to be two pipe characters and added escape options to allow setting Boolean positive and negative values. Optimized the code to remove redundancies, decrease the overall size, and speed up processing. Restructured the help text to make it more concise so that it can be used easier in tool tip popups. Also, added code to determine system decimal and thousands separators.

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

This is my Custom Function and I want to edit it


help me
i have problem :
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

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

Your Name:
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 2 + 6 =
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. ContainsAllExact ( Text ; SearchStrings )
  (Wed, Apr 26, 12:23pm)
2. ContainsExact ( Text ; SearchStrings )
  (Wed, Apr 26, 12:17pm)
3. TimeCorrect ( TimeEntry )
  (Wed, Apr 26, 9:41am)
4. ar_rm_diacritics ( text )
  (Tue, Apr 25, 5:22am)
5. ExplodeJSON ( JSON )
  (Sat, Apr 22, 12:46am)
6. TruncateStringWithBreaks(String_with_breaks; String_without_breaks)
  (Mon, Apr 17, 3:17am)
7. AS_Photos_Export ( Export_Path ; options ; Album_Name )
  (Thu, Apr 13, 12:34am)
8. IsValidTFN ( text )
  (Fri, Mar 31, 1:07am)

RSS Feed of Custom Functions