FileMaker Pro

FileMaker Custom Functions

Expand FileMaker Pro with Custom Functions. Create a new custom function in FileMaker, enter the name and attributes of the corresponding bold header below, click the Select button below the text area, copy the highlighted text, and, paste it into the custom function calculation field. The code comments have usage instructions.*

GetValueIndex (valueList, valueString, matchCase, occurrence):

Retrieve the index of the matching valueString in the valueList. Returns 0 if not found. Optionally, toggle case-sensitive searching by passing a boolean through matchCase, and, search for a specific
valueString occurrence (a negative occurrence number causes backward search from the end of the valueList).

Examples:
ValueListIndex (“Alpha¶Bravo¶Charlie¶Echo”, “charlie”, False, 1) = 3
ValueListIndex (“Alpha¶Bravo¶Charlie¶Echo”, “charlie”, True, 1) = 0
ValueListIndex (“Danial¶Daniel¶Bill¶daniel¶Frank¶daniel¶Ed”, “Daniel”, True, -1) = 2

Let ([ vlist = "¶" & valueList & "¶"; vtext = "¶" & valueString & "¶";
pos = If ( occurrence = 0; 0; Position( vlist; vtext; If ( occurrence < 0; Length ( vlist ); 1 ); If ( occurrence < 0; -1; 1 ) ) );
index = If ( pos; ValueCount ( Left ( vlist; pos ) ); 0 );
match = Exact ( GetValue ( valueList; index ); valueString );
occurrence = Case ( pos = 0; 0; matchCase; If ( match; occurrence + If ( occurrence > 0; -1; 1 ); occurrence); occurrence + If ( occurrence > 0; -1; 1 ) );
vlist = Case ( occurrence < 0; Left ( valueList; pos - 1 ); occurrence > 0; Right ( valueList; Length ( valueList ) - ( pos + Length ( valueString ) )); "" );
idx2 = If ( occurrence ≠ 0; GetValueIndex ( vlist ; valueString ; matchCase ; occurrence ); 0 );
index = Case ( occurrence < 0; idx2; occurrence > 0; If ( idx2 = 0; 0; index + idx2); index )];

index

)

/*
GetValueIndex (valueList, valueString, matchCase, occurrence)

by Lewis C. Lorenz.

9-20-2011: Created
9-22-2011: Updated.

Retrieve the index of the matching valueString in the valueList. Returns 0 if not found.
Optionally toggle case-sensitive searching by passing a boolean through matchCase.
Optionally search for specific valueString occurrence (a negative occurrence number causes backward search from the end of the valueList).

Examples:
ValueListIndex ("Alpha¶Bravo¶Charlie¶Echo", "charlie", False, 1) = 3
ValueListIndex ("Alpha¶Bravo¶Charlie¶Echo", "charlie", True, 1) = 0
ValueListIndex ("James¶Bill¶James¶Frank¶James¶Ed", "james", False, -2) = 3
*/

FormatDateTime ( dateValue, timeValue, format ):

Format the dateValue and/or timeValue as specified in formatString. If dateValue or timeValue are empty current date or current time are used. Optionally, use the backslash as an escape code to include text not intended to be formatted. Formatting performed using PHP date() function format codes but with FileMaker conventions.

Examples:
FormatDateTime ( 10-31-2011, “”, “D., F j, Y”) = Mon., October 31, 2011
FormatDateTime ( 10-31-2011, 11:06:53, “Y-m-d His \zulu\”) = 2011-10-31 084621 zulu
FormatDateTime ( 10-31-2011, “”, “\today is the \dS\ day\”) = today is the 31st day

Let( [d = If ( IsEmpty( dateValue ); Get ( CurrentDate ); GetAsDate ( dateValue ) ); t = If ( IsEmpty( timeValue ); Get ( CurrentTime ); GetAsTime ( timeValue ) ); format = format & If ( Mod ( PatternCount ( format; "\\" ); 2 ); "\\"; "" ); // Append \ if count not even. f = Left ( format; 1 ); f = If ( f = "\\"; Middle ( format; 1; Position ( format; "\\"; 2; 1 ) ); f ); // Escape found, set to full escaped string. c = Case( f = "\\\\"; "\\"; // If a single blackslash has been escaped, make f a single backslash. Length ( f ) > 1; Middle ( f; 2; Position ( f; "\\"; 2; 1 ) - 2); // Get the contents. Exact ( f; "d" ) ; Right ( GetAsText ( 100 + Day ( d ) ); 2 ) ; Exact ( f; "D" ) ; Left ( DayName ( d ) ; 3 ) ; Exact ( f; "j" ) ; GetAsText ( Day ( d ) ) ; Exact ( f; "l" ) ; DayName ( d ) ; Exact ( f; "N" ) ; GetAsText ( If ( DayOfWeek ( d ) - 1 = 0; 7; DayOfWeek ( d ) - 1 ) ) ; Exact ( f; "S" ) ; If ( PatternCount ( "11 12 13"; Right ( GetAsText ( 100 + Day ( d ) ); 2 ) ); "th"; Choose ( Mod ( Day ( d ) ; 10 ) ; "th"; "st"; "nd"; "rd"; "th"; "th"; "th"; "th"; "th"; "th" ) ); Exact ( f; "w" ) ; GetAsText ( DayOfWeek ( d ) ) ; Exact ( f; "z" ) ; GetAsText ( DayOfYear ( d ) ) ; Exact ( f; "W" ) ; GetAsText ( WeekOfYear ( d ) ) ; Exact ( f; "F" ) ; MonthName ( d ) ; Exact ( f; "m" ) ; If ( Month ( d ) < 10; "0"; "" ) & GetAsText ( Month ( d ) ) ; Exact ( f; "M" ) ; Left ( MonthName ( d ) ; 3 ) ; Exact ( f; "n" ) ; GetAsText ( Month ( d ) ) ; Exact ( f; "t" ) ; GetAsText ( Day ( Date ( Month ( d ) + 1; 1; Year ( d ) ) - 1 ) ) ; Exact ( f; "L" ) ; GetAsText ( If ( Day ( Date ( 3; 1; Year ( d ) ) - 1 ) = 29; 1; 0 ) ) ; Exact ( f; "o" ) ; GetAsText ( Year ( d ) + Case ( WeekOfYear ( d ) = 1 and DayOfWeek ( Date ( 1; 1; Year ( d ) ) ) > 1; -1; WeekOfYear ( d ) > 52 and DayOfWeek ( Date ( 12; 31; Year ( d ) ) ) < 7 ; 1 ; 0 ) ) ; Exact ( f; "Y" ) ; GetAsText ( Year ( d ) ) ; Exact ( f; "y" ) ; Right ( GetAsText ( Year ( d ) ) ; 2 ) ; Exact ( f; "a" ) ; If ( GetAsNumber ( t ) > 43200; "pm"; "am" ) ; Exact ( f; "A" ) ; If ( GetAsNumber ( t ) > 43200; "PM"; "AM" ) ; Exact ( f; "g" ) ; GetAsText ( If ( Hour ( t ) > 12; Hour ( t ) - 12; Hour ( t ) ) ) ; Exact ( f; "G" ) ; GetAsText ( Hour ( t ) ) ; Exact ( f; "h" ) ; Right ( GetAsText ( 100 + If ( Hour ( t ) > 12; Hour ( t ) - 12; Hour ( t ) ) ); 2) ; Exact ( f; "H" ) ; Right ( GetAsText ( 100 + Hour ( t ) ); 2 ); Exact ( f; "i" ) ; Right ( GetAsText ( 100 + Minute ( t ) ); 2); Exact ( f; "s" ) ; Right ( GetAsText ( 100 + Seconds ( t ) ); 2); Exact ( f; "u" ) ; GetAsText ( GetAsNumber ( t ) ) ; f); // Prepare for recursive call. format = Right ( format; Length (format ) - Length ( f ) ); // Call if not empty. c = c & If ( IsEmpty ( format ); ""; FormatDateTime ( d; t; format ) )]; c ) /* FormatDateTime ( dateValue, timeValue, format ) by Lewis C. Lorenz 9-22-2011 Created 11-29-2011 Modified 12-4-11 Modified Returns the dateValue or timeValue formatted as specified in format. If dateValue or timeValue are empty current date or current time are used. Optionally use the backslash as an escape code to include text not intended to be formatted. Escaped \ is written \\, all other escaped text must be enclosed within an opening \ and a closing \. Formatting performed using PHP date() function format codes but with Filemaker conventions. Day ------- d - 01-31 D - Mon-Sun j - 1-31 l (lowercase 'L') - Sunday-Saturday N - 1 (Monday) ... 7 (Sunday). (PHP convention: ISO-8601 representation of day of week.) S - st, nd, rd or th (English ordinal suffix.) w - 1 (Sunday) ... 7 (Saturday). (PHP convention : 0 (for Sunday) through 6 (for Saturday).) z - 1-366. (PHP convention: 0-365.) Week ----- W - 1-54, week of year Jan. 1. (PHP convention: ISO-8601 week number of year, weeks starting on Monday.) Month ---- F - January-December m - 01-12 M - Jan-Dec n - 1-12 t - 28-31 Year ----- L - Leap year (1/0) o - year number. ISO-8601 year number. Y - 4 digit year y - 2 digit year Time ---- a - am/pm A - AM/PM g - Hour 1-12 G - Hour 0-23 h - Hour 01-12 H - Hour 00-23 i - Min 00-59 s - Sec 00-59 u - Microseconds Examples: FormatDateTime ( 9/21/2005, 11:06:53, "Y-m-d His \zulu\") = 2005-09-21 110653 zulu FormatDateTime ( 10-31-2011, "", "\today is the \dS\ day\") = today is the 31st day */

FormatNumber ( number, format ):

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

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

Let ( [ number = GetAsNumber ( number ); neg = number < 0; bool = PatternCount ( format; "\/" ); signs = "-¶-¶<¶>¶(¶)¶cr¶cr¶∆¶∆¶"; cmds = ",¶.¶1¶0¶1¶¶¶¶¶¶¶"; //OPTIONS cmds = If ( not bool; If ( PatternCount ( format; "\T" ); Middle ( format; Position ( format; "\T"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\T"; 1; 1 ); 2 ) - ( Position ( format; "\T"; 1; 1) + 2 ) ); "," ) & "¶" & If ( PatternCount ( format; "\D" ); Middle ( format; Position ( format; "\D"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\D"; 1; 1 ); 2 ) - ( Position ( format; "\D"; 1; 1) + 2 ) ); "." ) & "¶" & If ( PatternCount ( format; "\R" ); Middle ( format; Position ( format; "\R"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\R"; 1; 1 ); 2 ) - ( Position ( format; "\R"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\Z" ); Middle ( format; Position ( format; "\Z"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\Z"; 1; 1 ); 2 ) - ( Position ( format; "\Z"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\F" ); Middle ( format; Position ( format; "\F"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\F"; 1; 1 ); 2 ) - ( Position ( format; "\F"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\SL" ); Middle ( format; Position ( format; "\SL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\SL"; 1; 1 ); 2 ) - ( Position ( format; "\SL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\SR" ); Middle ( format; Position ( format; "\SR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\SR"; 1; 1 ); 2 ) - ( Position ( format; "\SR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\NL" ); Middle ( format; Position ( format; "\NL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\NL"; 1; 1 ); 2 ) - ( Position ( format; "\NL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\NR" ); Middle ( format; Position ( format; "\NR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\NR"; 1; 1 ); 2 ) - ( Position ( format; "\NR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\PL" ); Middle ( format; Position ( format; "\PL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\PL"; 1; 1 ); 2 ) - ( Position ( format; "\PL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\PR" ); Middle ( format; Position ( format; "\PR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\PR"; 1; 1 ); 2 ) - ( Position ( format; "\PR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\C" ); Middle ( format; Position ( format; "\C"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\C"; 1; 1 ); 2 ) - ( Position ( format; "\C"; 1; 1) + 2 ) ); "" ); $cmds ); format = If ( not bool and PatternCount ( format; "\\" ); Substitute ( format ; ["\t"; "\T" ]; ["\d"; "\D" ]; ["\r"; "\R" ]; ["\z"; "\Z" ]; ["\f"; "\F" ]; ["\s"; "\S" ]; ["\n"; "\N" ]; ["\p"; "\P" ]; ["\c"; "\C" ]; ["\Sl"; "\SL" ]; ["\Sr"; "\SR" ]; ["\Nl"; "\NL" ]; ["\Nr"; "\NR" ]; ["\Pl"; "\PL" ]; ["\Pr"; "\PR" ]; ["\T" & GetValue ( cmds; 1 ) & "\\"; ""]; ["\D" & GetValue ( cmds; 2 ) & "\\"; ""]; ["\R" & GetValue ( cmds; 3 ) & "\\"; ""]; ["\Z" & GetValue ( cmds; 4 ) & "\\"; ""]; ["\F" & GetValue ( cmds; 5 ) & "\\"; ""]; ["\SL" & GetValue ( cmds; 6 ) & "\\"; ""]; ["\SR" & GetValue ( cmds; 7 ) & "\\"; ""]; ["\NL" & GetValue ( cmds; 8 ) & "\\"; ""]; ["\NR" & GetValue ( cmds; 9 ) & "\\"; ""]; ["\PL" & GetValue ( cmds; 10 ) & "\\"; ""]; ["\PR" & GetValue ( cmds; 11 ) & "\\"; ""]; ["\C" & GetValue ( cmds; 12 ) & "\\"; ""] ); format ); precision = Abs ( GetAsNumber ( Substitute ( format; [ GetValue ( cmds; 1 ); ""]; [ GetValue ( cmds; 2 ); ""] ) ) ); number = If ( PatternCount (format; "%" ); Abs ( number ) * 100; Abs ( Number ) ); number = If ( GetValue ( cmds; 3 ) and precision > 0; Round ( number ; precision ); number ); numbers = Substitute ( If ( PatternCount ( format; GetValue ( cmds; 1 ) ); NumToJText ( Int (number ); 1; 0 ); Int ( number ) ); ","; GetValue ( cmds; 1 ) ) & "¶" & Substitute ( GetAsText ( Mod ( number ; Int ( number ) ) ); GetValue ( cmds; 2 ); "" ); // NOTATIONS cmds = If ( IsEmpty ( GetValue ( cmds; 8 ) ) and IsEmpty ( GetValue ( cmds; 9 ) ); MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( If ( PatternCount ( format; GetValue ( cmds; 2) ); Left ( format; GetAsNumber ( Position (format; GetValue ( cmds; 2); 1; 1 ) ) - 1 ); Substitute ( format; If ( PatternCount (format; "%"); "%"; "" ); "" ) ) & "¶" & If ( PatternCount ( format; GetValue ( cmds; 2) ); Middle ( format; GetAsNumber ( Position (format; GetValue ( cmds; 2); 1; 1 ) ) + 1; Length ( format ) ); If ( PatternCount (format; "%"); "%"; "" ) ); [GetValue ( cmds; 1 ); ""]; [ GetAsText ( precision ); ""] ) & "¶" & MiddleValues ( cmds ; 10 ; 3 ); cmds ); // NEGATIVE SIGN INDEX ndx = If ( neg; Case ( PatternCount ( MiddleValues ( cmds; 8; 2) ; "-" ); 1; PatternCount ( MiddleValues ( cmds; 8; 2); "<" ) or PatternCount ( MiddleValues ( cmds; 8; 2); ">" ); 3; PatternCount ( MiddleValues ( cmds; 8; 2); "(" ) or PatternCount ( MiddleValues ( cmds; 8; 2); ")" ); 5; PatternCount ( MiddleValues ( cmds; 8; 2); "cr" ); 7; PatternCount ( MiddleValues ( cmds; 8; 2); "∆" ); 9; 1 ); // Defaults to minus sign. "" ); // REMOVE UNUSED SIGNS cmds = If ( neg; MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( MiddleValues ( cmds ; 8 ; 2 ) ; [ If ( ndx ≠ 1; GetValue ( signs; 1 ); "" ); "" ]; [ If ( ndx ≠ 1; GetValue ( signs; 2 ); "" ); "" ]; [ If ( ndx ≠ 3; GetValue ( signs; 3 ); "" ); "" ]; [ If ( ndx ≠ 3; GetValue ( signs; 4 ); "" ); "" ]; [ If ( ndx ≠ 5; GetValue ( signs; 5 ); "" ); "" ]; [ If ( ndx ≠ 5; GetValue ( signs; 6 ); "" ); "" ]; [ If ( ndx ≠ 7; GetValue ( signs; 7 ); "" ); "" ]; [ If ( ndx ≠ 7; GetValue ( signs; 8 ); "" ); "" ]; [ If ( ndx ≠ 9; GetValue ( signs; 9 ); "" ); "" ]; [ If ( ndx ≠ 9; GetValue ( signs; 10 ); "" ); "" ] ) & MiddleValues ( cmds ; 10 ; 3 ); cmds ); // PROXIMITY cmds = If ( neg and IsEmpty ( GetValue ( cmds; 10 ) ) and IsEmpty ( GetValue ( cmds; 11 ) ); MiddleValues ( cmds; 1; 9 ) & If ( ndx = 1 and not PatternCount ( format; GetValue ( signs; ndx ) ); "1¶1"; GetAsText ( Position ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ); 1; 1 ) = (Length ( GetValue ( cmds; 8 ) ) + 1) - Length ( GetValue ( signs; ndx ) ) ) & "¶" & GetAsText ( Position ( GetValue ( cmds; 9 ); GetValue ( signs; ndx + 1 ); 1; 1 ) = 1) ) & "¶" & MiddleValues ( cmds; 12; 1 ); cmds ); // Leave unchanged. // SIGNS cmds = If ( neg and IsEmpty ( GetValue ( cmds; 6 ) ) and IsEmpty ( GetValue ( cmds; 7 ) ); MiddleValues ( cmds; 1; 5 ) & Case ( ndx = 1; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) or not PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx ) ); GetValue ( signs; ndx ); "" ); // Left or default left. ndx = 3 or ndx = 5; GetValue ( signs; ndx ); ndx = 7; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) and PatternCount ( format; GetValue ( cmds; 2 ) ); "CR "; "" ); ndx = 9; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) ; "▲"; "" ); "" ) & "¶" & Case ( ndx = 1; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ); GetValue ( signs; ndx +1 ); "" ); ndx = 3 or ndx = 5; GetValue ( signs; ndx +1 ); ndx = 7; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ) or not PatternCount ( format; GetValue ( cmds; 2 ) ); " CR"; "" ); ndx = 9; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ); "▲"; "" ); "" ) & "¶" & MiddleValues ( cmds; 8; 5 ); cmds ); // Leave unchanged. // REMOVE SIGNS FROM NOTATIONS cmds = MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( MiddleValues ( cmds ; 8 ; 2 ); ["<"; ""]; [">"; ""]; ["("; ""]; [")"; ""]; ["cr"; ""]; ["∆"; ""]; ["-"; ""] ) & MiddleValues ( cmds ; 10 ; 3 ); // RESULT NUMBER r = If ( not GetAsBoolean ( GetValue ( cmds; 4 ) ) and GetAsNumber ( GetValue ( numbers; 1 ) ) = 0 and GetAsNumber ( GetValue ( numbers; 2 ) ) = 0; ""; GetValue ( numbers; 1 ) & If ( PatternCount ( format; GetValue ( cmds; 2 ) ) ; Case ( IsEmpty ( precision ); GetValue ( cmds; 2 ) & GetValue ( numbers; 2 ); // Show full unaltered decimal. precision ≥ 1; GetValue ( cmds; 2 ) & Left ( GetValue ( numbers; 2 ); precision ) & If ( GetAsBoolean ( GetValue ( cmds; 5 ) ); Right ( GetAsText ( 10 ^ precision ); precision - Length ( GetValue ( numbers; 2 ) ) ); "" ); "" ); // Show no decimal if precision is 0. "" ) ); // Show no decimal if not requested ) Hide zero number end ). // RESULT NOTATIONS AND SIGNS r = If ( IsEmpty ( r ); ""; Case ( bool; If ( GetAsBoolean ( number ); Left ( format; Position ( format; "\/"; 1; 1 ) - 1); Middle ( format; Position ( format; "\/"; 1; 1 ) + 2; Length ( format ) ) ); neg; If ( GetAsBoolean ( GetValue ( cmds; 10 ) ); GetValue ( cmds; 8 ) & GetValue ( cmds; 6 ) ; GetValue ( cmds; 6 ) & GetValue ( cmds; 8 ) ) & r & If ( GetAsBoolean ( GetValue ( cmds; 11 ) ); GetValue ( cmds; 7 ) & GetValue ( cmds; 9 ); GetValue ( cmds; 9 ) & GetValue ( cmds; 7 ) ); // End of is negative. GetValue ( cmds; 8 ) & r & GetValue ( cmds; 9 ) ) ); // Default . // COLOR RGB PREPARATION cmds = If ( neg and not IsEmpty ( GetValue ( cmds; 12 ) ); MiddleValues ( cmds ; 1 ; 11 ) & Substitute ( MiddleValues ( cmds ; 12 ; 1 ); ","; "¶" ); cmds ); // Leave unchanged. // RESULT COLOR r = If ( IsEmpty ( r ); ""; If ( neg and ( not IsEmpty ( GetValue ( cmds; 12 ) ) or not IsEmpty ( GetValue ( cmds; 13 ) ) or not IsEmpty ( GetValue ( cmds; 14 ) ) ); TextColor ( r; RGB ( GetAsNumber ( GetValue ( cmds; 12 ) ); GetAsNumber ( GetValue ( cmds; 13 ) ); GetAsNumber ( GetValue ( cmds; 14 ) ) ) ); r ) ) ]; r // Return result. ) /* FormatNumber ( number, format ) Returns text string of number in the requested format. by Lewis C. Lorenz 11-11-2011 Created 11-15-2011 Modified number = number to format. format = format string. Function allows complete FileMaker Pro number formatting using an intuitive format structure. Inclusion, and placement, of the following characters in the format string controls the resulting formatted number string. Number formatting. , (comma/thousands separator) = integer portion of the number is "thousands separated". . (period/decimal point) = controls display of the number decimal when used in conjunction with included, or omitted, # (number). # (number) = controls the count of digits of the number decimal to display (also specifies the rounding precision): 0 = Number decimal and decimal point is not displayed. Enables positioning of negative signs and notations. 1 ... n = Truncation and rounding precision of the number decimal. "" = Decimal point with no number displays the unaltered number decimal. Negative signs control the formatted result of negative numbers. The signs listed below are in order of precedence. Any lower-precedence signs also in the format string are ignored during processing and do not appear in the formatted result. Placement of minus sign, credit and delta symbols can be placed on the left or right of the formatted number by placing them to the left or right of an included decimal point in the format string. Negative signs will be placed at their default location when no decimal point is included. - = Minus sign (default). Default position is at the left. <> = Angle brackets. Surrounds the number. () = Parentheses. Surrounds the number. CR = Credit symbol. Default position is at the right. ∆ = Delta symbol. Default position is at the left. (Darkened triangle is used.) Boolean formatting. \/ = escaped fore-slash divides boolean results. Left side is returned if number is true, right side is returned if number is false. 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 point in the format string. Notations will be placed at their default position when no decimal point is included. % = Percent sign. Inclusion in format string forces multiplication of the number by 100 during processing. Default position is at the right. $, #, etc. = Dollar and pound signs, and other characters, can be included in your formatted number. Default position is at the left. Escaped characters can be used to override defaults and add formatting. Enclose each passed option in an ending escape (backslash). \T = thousands separator. Default is , (comma). \D = decimal point. Default is . (period). \R = Round to passed precision (default is true). \Z = Show number if zero (default is true). \F = Fill decimal with zeros to length of passed precision (default is true). \SL = Negative sign to appear on left side of number (default is minus sign). \SR = Negative sign to appear on right side of number. \NL = Notation to appear on left side of number. \NR = Notation sign to appear on right side of number. \PL = Proximity of negative sign on left side of number (default is next to number). \PR = Proximity of negative sign on right side of number (default is next to number). \C = Comma separated RGB number string to return colored negative 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 */

* Get to the custom function management window by choosing Manage Custom Functions under the File menu.

Leave a Reply Cancel reply

Recent Posts

Recent Comments

Archives

Categories

Administer