insert_commas ( number )
Insert commas in a number
Average rating: 4.3 (29 votes) Log in to vote
|
Matthew Stetson - Show more from this author
Antidote Solutions http://www.antidotesolutions.com |
Sample input:
Insert_Commas ( 1000000 )
Sample output:
1,000,000
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Inserts commas before every third digit before a decimal point. Uses recursion to handle any number, including negative values and decimals.
Comments
 |
Trevor Yancey, Scottsdale, AZ Apr 30, 2009 |
|
| There seems to be a problem with this function when it formats negative numbers with three or more digits. When I apply this function to a number field that contains a negative number such as -300, it results in the number being formatted as 00,300 instead of -300. Has anyone else encountered this problem? | ||
|
Trevor Yancey, Scottsdale, AZ Apr 30, 2009 |
|
| I figured out how to fix the formula. The problem with the original formula is that the function Length ( number ) counts the minus sign as a digit, so a number like -300 has four digits, which makes the original formula format the number improperly. The corrected formula is: Case( Int( number ) <> number; insert_commas( Int( number ) ) & GetAsText( Abs( number ) - Abs( Int( number ) ) ); Length( Abs( number ) ) > 3; insert_commas( Left( number; Length( number ) - 3 ) ) & "," & Right( number; 3 ); number ) |
||
|
thom droz, Fort Lauderdale Nov 30, 2010 |
|
| Function adds a 0 to number if a comma is entered by user | ||
|
thom droz, Fort Lauderdale Nov 30, 2010 |
|
| this work around resolved issue Case( Int( number ) <> number; Insert_Commas( Int( number ) ) & GetAsText( Abs( number ) - Abs( Int( number ) ) ); Length( number ) > 3; Insert_Commas( Left( number; Length( Substitute ( number ; "," ;"" ) ) - 3 ) ) & "," & Right( Substitute ( number ; "," ;"" ); 3 ); number ) |
||
 |
Martin Spanjaard, Trias Digitaal May 26, 2011 |
|
| Just uploaded my function formatN, which does the more or less the same as yours, and works more or less in the same way, but is easier to read. Forms the kernel of my other function formatAmount. formatN( naturalNumber, separator ) = Let([ N = GetAsText( naturalNumber ) ; l = Length( N ) ]; Case( l > 3 ; FormatN( Left( N ; l - 3 )) & separator & Right( N ; 3 ) ; N ) ) |
||
|
indiguy, india Jan 11, 2014 |
|
| what if i wanted to use numbers in this format 10,00,00,000 ????? pls show some way..using fmp12 adv |
||
|
Chris Bishop, Los Angeles Mar 14, 2017 |
|
| Most of these functions screw up if the input is text and contains either a user-entered comma or ".00" at the end (in the case you're passing a dollar amount). This one is more thorough and should work with any format of a number: f_commas ( number ) Let ( [ nums = "0123456789" ; p = Position ( number ; "." ; 1 ; 1 ) ; p = If ( p <= 0 ; Length ( number ) + 1 ; p ) ; p2 = Position ( number ; "," ; 1 ; 1 ) ; p = If ( p2 > 0 ; p2 ; p ) ; n1 = Middle ( number ; p - 1 ; 1 ) ; n2 = Middle ( number ; p - 2 ; 1 ) ; n3 = Middle ( number ; p - 3 ; 1 ) ; n4 = Middle ( number ; p - 4 ; 1 ) ] ; If ( p > 4 and Exact ( Filter ( n1 ; nums ) ; n1 ) and Exact ( Filter ( n2 ; nums ) ; n2 ) and Exact ( Filter ( n3 ; nums ) ; n3 ) and Exact ( Filter ( n4 ; nums ) ; n4 ) ; f_commas ( Left ( number ; p - 4 ) & "," & Middle ( number ; p - 3 ; 99999999 ) ) ; number ) ) |
||
|
Chris Bishop, Los Angeles Mar 14, 2017 |
|
| More concise version (plus can choose 2, 3, 4 digits per comma): f_commas ( number ; spacing ) Let ( [ nums = "0123456789" ; p = Position ( number ; "." ; 1 ; 1 ) ; p = If ( p <= 0 ; Length ( number ) + 1 ; p ) ; p2 = Position ( number ; "," ; 1 ; 1 ) ; p = If ( p2 > 0 ; p2 ; p ) ; n = Middle ( number ; p - (spacing + 1) ; (spacing + 1) ) ] ; If ( p > (spacing + 1) and Exact ( Filter ( n ; nums ) ; n ) ; f_commas ( Left ( number ; p - (spacing + 1) ) & "," & Middle ( number ; p - (spacing) ; 99999999 ) ; spacing ) ; number ) ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.