FormatNumber ( number , format )

FormatNumber is a one-pass FileMaker Pro custom function that formats a number using intuitive, free-form format options that completely mimic FileMaker Pro’s number data type formatting. Formatting options can also be specified via escaped options. Returns text string of number in the requested format.

Parameters

• number = The number to be formatted.
• format = Text string specifying the returned format of the number.

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 ( 5973600, “^” ) = 5.9736e6
• FormatNumber ( 59703600, “~0 kg” ) = 59.7036e6 kg [engineering notation]

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

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

Boolean Formatting:

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

Notation Formatting:

• % – 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.

Escaped Options Formatting:

• \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 \\\\.

---

REVISION NOTES:

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.

FormatNumber code: Copy the selected code (click on Select All to highlight all the code) to the clipboard and paste it into a newly created Custom Function. Download code as a text file from here.

<!–raw–>Let ( [ nbr = SetPrecision ( GetAsNumber ( number ); 400 ); fmt = Substitute ( GetAsText ( format ); [ “¶”; “” ]; [ Char (10); “” ]; [ Char (13); “” ] );<br /><br /> sts = Middle ( NumToJText ( 1000; 1; 0 ); 2; 1 ); sds = Middle ( 3/2; 2; 1 );<br /><br /> neg = nbr < 0;<br /> sgn = “-¶-¶<¶>¶(¶)¶∆¶∆¶CR¶CR”;<br /><br /> /* OPTIONS Thousands, Decimal, Rounding, Zero, Fill, Sign Left, Sign Right, Proximity Left, Proximity Right, Notation Left, Notation Right, Color, Exponentiation (0=None, 1=Sci, 2=Eng), Boolean Positive, Boolean Negative. */<br /><br /> opt = List ( “¶”; True; True; True; “¶¶¶¶¶”; “0,0,0”; “¶¶” );<br /><br /> /* ESCAPED OPTIONS. Escaped format options override dissimilar defaults… */<br /><br /> opt =<br /><br /> Let ( p = Position ( fmt; “\T”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 1 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\D”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 2 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\R”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 3 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\Z”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 4 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\F”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 5 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\SL”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 6 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\SR”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 7 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\PL”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 8 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\PR”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 9 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\NL”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 10 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\NR”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 11 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\C”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 12 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\E”; 1; 1); If ( p; Middle ( fmt; p + 2; Position ( fmt; “\\”; p; 2 ) – ( p + 2 ) ); GetValue ( opt; 13 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\BP”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 14 ) ) ) & “¶” &<br /><br /> Let ( p = Position ( fmt; “\BN”; 1; 1); If ( p; Middle ( fmt; p + 3; Position ( fmt; “\\”; p; 2 ) – ( p + 3 ) ); GetValue ( opt; 15 ) ) );<br /><br /> /* … clear escaped options. */<br /><br /> fmt = If ( PatternCount ( fmt; “\\” );<br /><br /> Substitute ( fmt ; [“\t”; “\T” ]; [“\d”; “\D” ]; [“\r”; “\R” ]; [“\z”; “\Z” ]; [“\f”; “\F” ]; [“\s”; “\S” ]; [“\n”; “\N” ]; [“\p”; “\P” ]; [“\c”; “\C” ]; [“\e”; “\E” ]; [“\b”; “\B” ]; [“\Sl”; “\SL” ]; [“\Sr”; “\SR” ]; [“\Nl”; “\NL” ]; [“\Nr”; “\NR” ]; [“\Pl”; “\PL” ]; [“\Pr”; “\PR” ]; [“\Bp”; “\BP” ]; [“\Bn”; “\BN” ]; [“\T” & GetValue ( opt; 1 ) & “\\”; “”]; [“\D” & GetValue ( opt; 2 ) & “\\”; “”]; [“\R” & GetValue ( opt; 3 ) & “\\”; “”]; [“\Z” & GetValue ( opt; 4 ) & “\\”; “”]; [“\F” & GetValue ( opt; 5 ) & “\\”; “”]; [“\SL” & GetValue ( opt; 6 ) & “\\”; “”]; [“\SR” & GetValue ( opt; 7 ) & “\\”; “”]; [“\NL” & GetValue ( opt; 10 ) & “\\”; “”]; [“\NR” & GetValue ( opt; 11 ) & “\\”; “”]; [“\PL” & GetValue ( opt; 8 ) & “\\”; “”]; [“\PR” & GetValue ( opt; 9 ) & “\\”; “”]; [“\C” & GetValue ( opt; 12 ) & “\\”; “”]; [“\E” & GetValue ( opt; 13 ) & “\\”; “”]; [“\BP” & GetValue ( opt; 14 ) & “\\”; “”]; [“\BN” & GetValue ( opt; 15 ) & “\\”; “”] );<br /><br /> fmt );</p><br /> <p>/* BOOLEAN. Set opt(14) and opt(15) from fmt. */<br /><br /> opt = If ( PatternCount ( fmt; “||” ) and IsEmpty ( GetValue ( opt; 14 ) ) and IsEmpty ( GetValue ( opt; 15 ) );<br /><br /> MiddleValues ( opt; 1; 13 ) & LeftValues ( Substitute ( fmt; [“||”;”¶”] ); 2 );<br /><br /> opt );<br /><br /> boo = not IsEmpty ( GetValue ( opt; 14 ) ) or not IsEmpty ( GetValue ( opt; 15 ) );</p><br /> <p>/* EXPONENTIATION. Set opt(13) from fmt: 0 = none, 1 = Scientific., 2 = Engineeering. */<br /><br /> opt = If ( IsEmpty ( GetValue ( opt; 13 ) ); Let ( e = Filter ( fmt; “^~” );<br /><br /> MiddleValues ( opt; 1; 12 ) & GetAsText ( Position ( “^~”; Left ( e; 1 ); 1; 1 ) ) & “¶” & MiddleValues ( opt; 14; 2 ) );<br /><br /> opt );<br /><br /> /* … clear exponentiation codes from fmt. */<br /><br /> fmt = Substitute ( fmt; [ “^”; “” ]; [ “~”; “” ] );</p><br /> <p>/* PRECISION. Digits in fmt used for precision and rounding (n) or positioning (0). */<br /><br /> dgt = Let ( n = GetAsNumber ( Filter ( fmt; “0123456789” ) ); If ( n > 400; 400; n ) );</p><br /> <p>/* NEGATIVE SIGN INDEX. Get index to 1st instance of neg sign pair in sgn. */<br /><br /> ndx = Let ( n = Position ( “–<>()∆”; Left ( Filter ( fmt; “-<>()∆” ); 1 ); 1; 1 ); Case ( n = 0 and PatternCount ( fmt; “cr” ); 9; n = 0 ; 1; not Mod ( n; 2 ); n – 1; n ) );<br /><br /> /* … clear non-ndx signs from fmt. */<br /><br /> fmt = Substitute ( fmt; [ If ( ndx ≠ 1; GetValue ( sgn; 1 ); “” ); “” ]; [ If ( ndx ≠ 1; GetValue ( sgn; 2 ); “” ); “” ]; [ If ( ndx ≠ 3; GetValue ( sgn; 3 ); “” ); “” ]; [ If ( ndx ≠ 3; GetValue ( sgn; 4 ); “” ); “” ]; [ If ( ndx ≠ 5; GetValue ( sgn; 5 ); “” ); “” ]; [ If ( ndx ≠ 5; GetValue ( sgn; 6 ); “” ); “” ]; [ If ( ndx ≠ 7; GetValue ( sgn; 7 ); “” ); “” ]; [ If ( ndx ≠ 7; GetValue ( sgn; 8 ); “” ); “” ]; [ If ( ndx ≠ 9; GetValue ( sgn; 9 ); “” ); “” ]; [ If ( ndx ≠ 9; GetValue ( sgn; 10 ); “” ); “” ] ); </p><br /> <p>/* SEPARATORS. Set opt(1) and (2) from fmt. Use system defaults, if empty. */<br /><br /> opt = If ( IsEmpty ( GetValue ( opt; 1 ) ) and PatternCount ( fmt; sts ); sts; GetValue ( opt; 1 ) ) & “¶” & // Thousands.<br /><br /> If ( IsEmpty ( GetValue ( opt; 2 ) ) and PatternCount ( fmt; sds ); sds; GetValue ( opt; 2 ) ) & “¶” & // Decimal.<br /><br /> MiddleValues ( opt; 3; 13 );</p><br /> <p>/* FORMAT LIST. Divide fmt on decimal or zero for SIGNS, PROXIMITY and NOTATIONS. If no decimal value 1 is fmt. */<br /><br /> fml = Let ( [ d = GetValue ( opt; 2 ); s = Case ( not IsEmpty ( d ); d; dgt = 0; “0”; “” ); n = If ( IsEmpty ( s ); 0; Position ( fmt; s; 1; 1 ) ) ]; If ( GetAsBoolean ( n ); Left ( fmt; n – 1 ) & “¶” & Right ( fmt; Length ( fmt ) – n ); fmt ) );</p><br /> <p>/* SIGNS. Set opt(6) and opt(7) from fmt. */<br /><br /> opt = If ( neg and IsEmpty ( GetValue ( opt; 6 ) ) and IsEmpty ( GetValue ( opt; 7 ) );<br /><br /> MiddleValues ( opt; 1; 5 ) &<br /><br /> Let ( [ bls = not IsEmpty ( Filter ( Upper ( GetValue ( fml; 1 ) ); MiddleValues ( sgn; ndx; 2 ) ) ); brs = not IsEmpty ( Filter ( Upper ( GetValue ( fml; 2 ) ); MiddleValues ( sgn; ndx; 2 ) ) ); bds = “▲”; sl = If ( ndx = 7; bds; GetValue ( sgn; ndx ) ); sr = If ( ndx = 7; bds; GetValue ( sgn; ndx + 1 ) ) ];<br /><br /> If ( ( ndx = 1 and ( bls or not brs ) ) or ( ndx = 3 or ndx = 5 or ( ndx = 7 and bls ) ) or ( ndx = 9 and ( bls and not IsEmpty ( GetValue ( opt; 2 ) ) ) ); sl; “” ) & “¶” &<br /><br /> If ( ( ndx = 3 or ndx = 5 or ( ( ndx = 1 or ndx = 7 ) and brs ) ) or ( ndx = 9 and ( brs or IsEmpty ( GetValue ( opt; 2 ) ) ) ); sr; “” )<br /><br /> ) & “¶” & // End Let.<br /><br /> MiddleValues ( opt; 8; 8 );<br /><br /> opt ); // Unaltered.</p><br /> <p>/* PROXIMITY. Set opt(8) and (9) from fmt. Controls proximity of neg sign to number and on which side it is displayed. True means next to the number inside the notation, false means outside of the notation. Determined by checking whether the sgn (ndx) is far right of the left side of fmt or 1st character on the right side of fmt. */<br /><br /> opt = If ( neg and IsEmpty ( GetValue ( opt; 8 ) ) and IsEmpty ( GetValue ( opt; 9 ) );<br /><br /> MiddleValues ( opt; 1; 7 ) &<br /><br /> If ( ndx = 1 and not PatternCount ( fml; GetValue ( sgn; ndx ) );<br /><br /> “1¶1”; // Default inside.<br /><br /> GetAsText ( Position ( GetValue ( fml; 1 ); GetValue ( sgn; ndx ); 1; 1 ) = (Length ( GetValue ( fml; 1 ) ) + 1 ) – Length ( GetValue ( sgn; ndx ) ) ) & “¶” &<br /><br /> If ( ValueCount ( fml ) = 1; “0”; GetAsText ( Position ( GetValue ( fml; 2 ); GetValue ( sgn; ndx + 1 ); 1; 1 ) = 1 ) ) ) & “¶” &<br /><br /> MiddleValues ( opt; 10; 6 );<br /><br /> opt ); // Unaltered.</p><br /> <p>/* REMOVE SIGNS. The used sign, whatever it is, is removed. Only the notations remain to be stored.*/<br /><br /> fml = Substitute ( fml; [“<“; “”]; [“>”; “”]; [“(“; “”]; [“)”; “”]; [“cr”; “”]; [“Cr”; “”]; [“cR”; “”]; [“CR”; “”]; [“∆”; “”]; [“-“; “”] );</p><br /> <p>/* NOTATION ($, %, #, etc.). Set opt(10) and opt(11) from fml. Stores notation for left and right side of number.<br /><br /> Opt(10) is set to fmt left of the separator if present (including %), else it is set to fmt without % (default is all but % is displayed on the left – in opt(10)).<br /><br /> Opt(11) is set to fmt right of separator if present, else it is set to % if in fmt or blank if not (default is % is displayed on right – in opt(11)). */<br /><br /> opt = If ( IsEmpty ( GetValue ( opt; 10 ) ) and IsEmpty ( GetValue ( opt; 11 ) );<br /><br /> MiddleValues ( opt ; 1 ; 9 ) &<br /><br /> Substitute ( If ( ValueCount ( fml ) = 2; GetValue ( fml; 1 ); Substitute ( GetValue ( fml; 1 ); [ “%”; “” ] ) ) & “¶” &<br /><br /> If ( ValueCount ( fml ) = 2; GetValue ( fml; 2 ); If ( PatternCount ( GetValue ( fml; 2 ); “%”); “%”; “” ) ); [ GetValue ( opt; 1 ); “” ]; [ GetAsText ( dgt ); “” ] ) & “¶” &<br /><br /> MiddleValues ( opt ; 12 ; 4 );<br /><br /> opt ); // Unaltered.</p><br /> <p>// NUMBER PROCESSING.</p><br /> <p>/* PERCENTAGE. Multiply nbr by 100 if % is passed in fmt. Allow setting % notation via escaped option opt(8) and (9) without forcing multiplication. */<br /><br /> nbr = If ( boo; nbr; If ( PatternCount ( fml; “%” ); Abs ( nbr ) * 100; Abs ( nbr ) ) );</p><br /> <p>/* ROUNDING/PRECISION. If digit > 0, round and/or truncate nbr to that precision. */<br /><br /> nbr = Case ( boo; nbr; GetAsBoolean ( GetValue ( opt; 3 ) ) and dgt > 0; Round ( nbr ; dgt ); dgt > 0; Truncate ( nbr ; dgt ); nbr );</p><br /> <p>/* NUMBERS LIST. Split number into integer w/ or w/o “,” and decimal without “.”. */<br /><br /> eno = Position ( nbr; “e”; 1; 1 ); // Check to convert passed scientific E notation.<br /><br /> nbs = If ( eno; Let ( [ e = GetAsNumber ( Right ( nbr; Length ( nbr ) – eno ) ); z = Substitute ( 10 ^ Abs ( e ) – 1; “9”; “0” ); n = Filter ( GetAsNumber ( sds & Left ( nbr; eno – 1 ) ); “1234567890” ) ];<br /><br /> If ( e > 0; Left ( n & z; e + 1 ) & “¶” & Middle ( n; e + 2; Length ( n ) ); “0¶” & Left ( z; Abs ( e ) – 1 ) & n )<br /><br /> ); //…else (not eno ).<br /><br /> If ( IsEmpty ( GetValue ( opt; 1 ) ) or GetAsBoolean ( GetValue ( opt; 13 ) ); GetAsText ( Int ( nbr ) ); Substitute ( NumToJText ( Int ( nbr ); 1; 0 ); [ sts; GetValue ( opt; 1 ) ] ) ) & “¶” & Let ( [ d = nbr & sds; p = Position ( d; sds; 1; 1 ) ]; Filter ( Right ( d; Length ( d ) – p ); “1234567890” ) )<br /><br /> );</p><br /> <p>/* NUMBER FORMAT. nbs is a 2 item list: integer and decimal. */<br /><br /> res = Let ( [ t = GetAsNumber ( GetValue ( opt; 13 ) ); iv = GetValue ( nbs; 1 ); i = GetAsNumber ( iv ); dv = GetValue ( nbs; 2 ); d = GetAsNumber ( dv ) ];<br /><br /> Case (<br /><br /> //****** Boolean.<br /><br /> boo; If ( GetAsBoolean ( nbr ); GetValue ( opt; 14 ); GetValue ( opt; 15 ) );<br /><br /> //****** Zero.<br /><br /> not i and not d; If ( not GetAsBoolean ( GetValue ( opt; 4 ) ); “”; “0” );<br /><br /> //****** Exponential (1 = scientific, 2 = engineering).<br /><br /> t; Let ( [ e = If ( i > 0; Int ( Log ( i ) ); Int ( Log ( GetAsNumber ( sds & dv ) ) ) – 1 ); n = Case ( i and d; iv & dv; i and not ( d ); Filter ( GetAsNumber ( sds & iv ); “1234567890” ); d; GetAsText ( d ); “” ); m = If ( t = 2; If ( e < 3; e; Mod ( e; 3 ) ); 0 ); dvv = Middle ( n; 2 + m; Length ( n ) ); n = If ( t = 2 and IsEmpty ( dvv ); n & Left ( “000”; 1 + m – Length ( n ) ); n ); e = If ( t = 2; Div ( e; 3 ) * 3; e ) ];<br /> Left ( n; 1 + m ) & If ( IsEmpty ( dvv ); “”; sds & Middle ( n; 2 + m; Length ( n ) ) ) & If ( e > 0; “e” & e; “” ) );<br /><br /> //****** Default as non-exponential number.<br /><br /> iv & Case (<br /><br /> IsEmpty ( GetValue ( opt; 2 ) ); “”; //****** No decimal requested.<br /><br /> IsEmpty ( dgt ); //****** Show full decimal<br /><br /> GetValue ( opt; 2 ) & dv;<br /><br /> dgt > 0; //****** Append count (dgt) of zeros to decimal.<br /><br /> GetValue ( opt; 2 ) & If ( GetAsBoolean ( GetValue ( opt; 5 ) ); Left ( dv & Substitute ( 10 ^ dgt – 1 ; 9 ; 0 ) ; dgt ); dv );<br /><br /> //****** Default, dgt (precision) is ‘0’ so no decimal.<br /><br /> “” ) ) );</p><br /> <p>/* NUMBER SIGNS AND NOTATIONS. Positioned where requested. */<br /><br /> res = If ( boo or IsEmpty ( res ); res; Let ( [ sl = GetValue ( opt; 6 ); sr = GetValue ( opt; 7 ); nl = GetValue ( opt; 10 ); nr = GetValue ( opt; 11 ) ];<br /><br /> If ( neg; If ( GetAsBoolean ( GetValue ( opt; 8 ) ); nl & sl ; sl & nl ) & res & If ( GetAsBoolean ( GetValue ( opt; 9 ) ); sr & nr; nr & sr ); // End of negative.<br /><br /> nl & res & nr ) ) ); // Case default .</p><br /> <p>/* NUMBER COLOR. Get, confirm, and apply RGB colors to result. */<br /><br /> res = If ( neg; Let ( [ c = LeftValues ( Substitute ( GetValue ( opt; 12 ) & “,,”; [ “,”; “¶” ] ); 3 ); r = GetAsNumber ( GetValue ( c; 1 ) ); r = If ( r > -1 and r < 256; r; 0 ); g = GetAsNumber ( GetValue ( c; 2 ) ); g = If ( g > -1 and g < 256; g; 0 ); b = GetAsNumber ( GetValue ( c; 3 ) ); b = If ( b > -1 and b < 256; b; 0 ) ]; TextColor ( res; RGB ( r; g; b ) ) ); res );</p> <p>// HELP TEXT<br /> hlp = If ( fmt ≠ “?”; “”; “FormatNumber ( number, format )¶¶<br /> NUMBER FORMATTING (Ex.: \”$,.2\”):¶<br /> , – (thousands separator) integer is \”thousands separated\”.¶<br /> . – (decimal separator) controls display of the number decimal.¶<br /> # – (number) rounding/truncation precision:¶<br /> 0 – integer only is displayed. Use to control negative signs and notations positioning.¶<br /> 1…n – rounding/truncation precision of decimal.¶<br /> . – decimal separator with no number displays the unaltered number decimal.¶<br /> ^ – (caret) formats number in scientific E notation.¶<br /> ~ – (tilde) formats number in engineering E notation.¶<br /> NEGATIVE SIGN FORMATTING (Ex.: \”.-\”):¶<br /> – – minus sign (default) (default: left).¶<br /> <> – angle brackets (default: left and right).¶<br /><br /> () – parentheses (default: left and right).¶<br /><br /> CR – credit symbol (default: right).¶<br /><br /> ∆ – delta symbol (default: left). (Dark delta is used.)¶<br /><br /> BOOLEAN FORMATTING (Ex.: \”Yes||No\”).¶<br /><br /> || – Divides boolean results. True number returns left value. False number returns right value.¶<br /><br /> NOTATIONS FORMATTING (Ex.: \”$.2 dollars\”).¶<br /><br /> % – percent sign. Force multiply number by 100 (default: right).¶<br /><br /> $, £, #, etc. – dollar, pound, hash, etc. (default: left).¶<br /><br /> ESCAPED OPTIONS FORMATTING (Ex.: \”\c255,,\\\”). Enclose each option with escapes, \\\, (backslashes).¶<br /><br /> \T – (text) thousands separator (default: system).¶<br /><br /> \D – (text) decimal separator (default: system).¶<br /><br /> \R – (boolean) rounding on (default: true).¶<br /><br /> \Z – (boolean) show number if zero (default: true).¶<br /><br /> \F – (boolean) pad decimal with zeros to precision (default: true).¶<br /><br /> \SL – (text) negative sign (-, <>, (), CR, ∆) on left of number (default: minus sign).¶<br /><br /> \SR – (text) negative sign on right of number (default: empty).¶<br /><br /> \PL – (boolean) negative sign on left side is next to number (default: true).¶<br /><br /> \PR – (boolean) negative sign on right side is next to number (default: true).¶<br /><br /> \NL – (text) notation ($, %, #, etc.) on left side of number (default: empty).¶<br /><br /> \NR – (text) notation on right side of number (default: empty).¶<br /><br /> \C – (integers) comma separated RGB number text (#,#,#) to color negative number (default: black).¶<br /><br /> \X – (integer) exponentiation: 0 = none, 1 = scientific, 2 = engineering (default: 0).¶<br /><br /> \BP – (text) positive response to boolean formatting.¶<br /><br /> \BN – (text) negative response to boolean formatting.”<br /><br /> ) // End of Help Preparation</p><br /> <p>];</p><br /> <p>If ( IsEmpty ( hlp ); res; hlp ) // Return result.</p><br /> <p>)</p><br /> <p>/*<br /><br /> FormatNumber ( number, format )</p><br /> <p>Returns a text string of the number in the requested format.</p><br /> <p>By Lewis C. Lorenz<br /><br /> Last updated: 2/11/2014 </p><br /> <p>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. </p><br /> <p>HELP:<br /><br /> Passing ‘?’ as the only content of ‘format’ returns a shortened version of this text.</p><br /> <p>NUMBER FORMATTING:<br /><br /> , – (thousands separator) integer portion of the number is “thousands separated”.<br /><br /> . – (decimal separator) displays of the decimal portion of the number.<br /><br /> # – (number) specifies the rounding precision (controls the count of decimal number digits to display):<br /><br /> 0 – number decimal and decimal separator is not displayed. Enables positioning of negative signs and notations.<br /><br /> 1…n – Truncation and rounding precision of the number decimal.<br /><br /> . – decimal separator only, with no number, displays the unaltered number decimal.<br /><br /> ^ – (caret) formats number in scientific E notation.<br /><br /> ~ – (tilde) formats number in engineering E notation.</p><br /> <p>NEGATIVE SIGN FORMATTING:<br /><br /> – – minus sign (default) (default: left).<br /><br /> <> – angle brackets (default: left and rigth).<br /><br /> () – parentheses (default: left and right).<br /><br /> CR – credit symbol (default: right).<br /><br /> ∆ – delta symbol (default: left). (Black delta is output.)<br /><br /> 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.</p><br /> <p>BOOLEAN FORMATTING:<br /><br /> || – two pipe characters divide boolean results. True number returns left side. False number returns right side.</p><br /> <p>NOTATION FORMATTING:<br /><br /> % – percent sign. Inclusion in ‘format’ forces multiplication of the number by 100 (default: right).<br /><br /> $, £, #, etc. – dollar, pound, hash, and any other characters, can be included in the formatted number (default: left).<br /><br /> 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.</p><br /> <p>ESCAPED OPTIONS FORMATTING:<br /><br /> \T – (text) thousands separator (default: system).<br /><br /> \D – (text) decimal separator (default: system).<br /><br /> \R – (boolean) round number to passed precision (default: true).<br /><br /> \Z – (boolean) show number if zero (default: true).<br /><br /> \F – (boolean) fill (pad) decimal with zeros to length of passed precision (default: true).<br /><br /> \SL – (text) negative sign (-, <>, (), CR, ∆) appearing on left side of number (default: minus sign).<br /><br /> \SR – (text) negative sign (-, <>, (), CR, ∆) appearing on right side of number (default: empty).<br /><br /> \PL – (boolean) proximity of negative sign on left side is next to number (default: true).<br /><br /> \PR – (boolean) proximity of negative sign on right side is next to number (default: true).<br /><br /> \NL – (text) notation sign ($, %, #, etc.) to appear on left side of number (default: empty).<br /><br /> \NR – (text) notation sign ($, %, #, etc.) to appear on right side of number (default: empty).<br /><br /> \C – (integers) comma-separated RGB number text (#,#,#) to color negative number (default: black).<br /><br /> \X – (integer) exponentiation formatting: 0 = none, 1 = scientific, 2 = engineering (default: 0).<br /><br /> \BP – (text) positive response to boolean formatting.<br /><br /> \BN – (text) negative response to boolean formatting.<br /><br /> Escaped options override free-form format, defaults and add formatting. Enclose each option with escape (backslash) characters \\\\.</p><br /> <p>Examples:<br /><br /> FormatNumber ( 14725.3675, “$,.2 US” ) = $14,725.37 US<br /><br /> FormatNumber ( -14725.3675, “$(,.2) US\r0\” ) = $(14,725.36) US [rounding off]<br /><br /> FormatNumber ( -14725.3675, “#,.\c255\” ) = #-14,725.3675 [text color is red]<br /><br /> FormatNumber ( 1, “Yes||No” ) = Yes<br /><br /> FormatNumber ( 5973600, “^” ) = 5.9736e6<br /><br /> FormatNumber ( 59703600, “~0 kg” ) = 59.7036e6 kg<br /><br /> */<!–/raw–>