Brian Dunning's FileMaker Custom Functions

NumberFormat ( Number ; DecimalPrecision ; ThousandSeparator )

to format numbers with decimal and thousand separator (option)

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

Agnes Barouh   Agnes Barouh - Show more from this author
Tic Tac
http://www.tictac.fr/CoinFileMaker/Page.html

Share on Facebook Share on Twitter

  Sample input:
NumberFormat ( "1021001,00125" ; 3 ; "." )
  Sample output:
1.021.001,001

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

** Nov 2014
If you want thousand separator for décimal, the last line change :
Substitute ( NumToJText ( Middle ( Nbr - Int ( Nbr ) & Substitute ( ( 10 ^ p ) - 1; 9; 0 ) ; 2 ; p ) ; 1; 0 ); ","; Sep )
instead of
Middle ( Nbr - Int ( Nbr ) & Substitute ( ( 10 ^ p ) - 1; 9; 0 ); 2; p ) )

** April 9/10 : calculation modified if n is empty return nothing
** December, 21. : calculation modified : change Filter ( 1,1 ; ".;" ) by Filter ( 1/2 ; ".;" ) for all language ( x,xx for French, x.xx English and all - [comma or point in number]) ) **
** December, 18. : calculation modified : add GetAsNumber() for get signe
for entry without 0 -> ,123 or 0,123 **

NumberFormat ( Number ; DecimalPrecision ; ThousandSeparator ) v1.0
is not recursive

to format numbers with decimal and thousand séparator (option)


//---------------------------------- Example

NumberFormat ( "1021001,00125" ; 3 ; "." ) > Result : 1.021.001,001
NumberFormat ( "69584" ; 2 ; " " ) > Result : 69 584,00

 

Comments

David   David, Tampa
Jan 19, 2010
This is a great function. How would you go about editing it so that one can show the decimals only if there are decimals? Much like the number formatting for a number field on a layout.

Great work!
 
Agnès   Agnès, Paris
Apr 9, 2010
Hello and Thank You !

"How would you go about editing it so that one can show the decimals only if there are decimals?"

It can do without adding a parameter to the function, perhaps simply with the following calculation, with this, each chose its resul ?

If ( int ( MyNum ) <> (MyNum) ;
NumbertFormat ( MyNum ; 2 ; "" ) ;
MyNum
)

I do not know...

Agnès
 
Tiago   Tiago, Antwerp
Aug 17, 2011
Nice man, Just what I needed :)
loved it
 
plop   plop, paris
Apr 25, 2012
thank you
 
kolvorok   kolvorok, Bergen, Norway
Oct 25, 2013
This was great! Thanks, France...:-)
 
Andy Smith   Andy Smith
Feb 11, 2015
Thank you, very useful.

For myself I needed it to use the existing precision of the number (when I pass "" as the precision param). I think that is what David (first comment) also required.

Just add this line below p = DecimalPrecision ;
p = If ( IsEmpty ( p ) and n ≠ Int ( n ) ; Length ( Abs ( n ) - Int ( n ) ) - If ( n < 0 ; 2 ; 1 ) ; p ) ;
 
Emeric   Emeric, Bordeaux
May 25, 2016
Un immense bravo et un grand merci !
 
Peter Wagemans   Peter Wagemans, Lesterius
Mar 23, 2018
Almost 10 years later, this function still rocks.
 
Agnès   Agnès, Paris
Mar 23, 2018
SHE did not get older ;)
glad she's still useful !
 
Fabrice Ricker   Fabrice Ricker, Kultur und Computer
Jul 26, 2019
Coucou, je me suis permis d'extrapoler un peu au fil des besoins.
Avec un "rendez à César ce qui est à César" en début de commentaire.
https://www.briandunning.com/cf/2283

Et merci encore, I still love this "trics".
 

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.

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: