Brian Dunning's FileMaker Custom Functions

insert_commas ( number )

Insert commas in a number

  Average rating: 4.3 (29 votes) Log in to vote

Matthew Stetson   Matthew Stetson
Antidote Solutions
http://www.antidotesolutions.com

Share on Facebook Share on Twitter

  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   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   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   thom droz, Fort Lauderdale
Nov 30, 2010
Function adds a 0 to number if a comma is entered by user
 
thom droz   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   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   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   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   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
)
)
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.