 insert_commas ( number )

Insert commas in a number     Average rating: 4.3 (29 votes) Log in to vote Matthew Stetson 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. Trevor Yancey, Scottsdale, AZApr 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, AZApr 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 LauderdaleNov 30, 2010 Function adds a 0 to number if a comma is entered by user thom droz, Fort LauderdaleNov 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 DigitaalMay 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, indiaJan 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 AngelesMar 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 AngelesMar 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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: