formatN ( naturalNumber ; separator )
formats a natural number with thousand separators
Average rating: 4.5 (31 votes) Log in to vote
|
Martin Spanjaard - Show more from this author
Trias Digitaal http://www.triasdigitaal.nl |
Sample input:
formatN( 89123456789123456789 ; "." )
Sample output:
89.123.456.789.123.456.789
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Recursive funtion to insert a separator (period or comma or whatever) between every 3 digits.
Expects a natural number, an integer > 0.
This custom function can be used as the heart of all sorts of number formatting functions, like my custom function 'formatAmount'
Comments
|
Martin Spanjaard, Trias Digitaal May 26, 2011 |
|
| Stupid forgot the separator in the recursion, it should be: Let([ N = GetAsText( naturalNumber ) ; l = Length( N ) ]; Case( l > 3 // as long as the length is greater then 3, // keep adding a separator before(!) // every three digits ; FormatN( Left( N ; l - 3 ) ; seprator ) & separator & Right( N ; 3 ) // add the remaining digits to the front // of the formatted number ; N ) ) |
||
 |
Agnès, France May 26, 2011 |
|
| Hello, without recursive, you can use NumToJText ( Number ; 1; 0 ) and substitute the coma whit your separator If you whant to control décimal you can saw this code : Substitute ( NumToJText ( int ( nbr ) ; 1; 0 ) ; "," ; sep ) & nbr - int ( nbr ) |
||
|
Miguel Angel Fernández, León / Spain Nov 8, 2013 |
|
| I've modified your custom function in order to allow decimals (comma separated) ------------------------------------------------------------------------------------------- Let([ D = Case ( PatternCount ( naturalNumber ; "," ) ; Right ( naturalNumber ; Length ( naturalNumber ) - Position ( naturalNumber ; "," ; 1 ; 1 ) + 1 ) ) ; // get the decimal part N = Left ( GetAsText( naturalNumber ) ; Length ( naturalNumber ) - Length ( D ) ) ; // natural number without the decimal part l = Length( N ) ]; Case( l > 3 // as long as the length is greater then 3, // keep adding a separator before(!) // every three digits ; FormatN( Left( N ; l - 3 ) ; separator ) & separator & Right( N ; 3 ) & D // and the decimal part if it exists // add the remaining digits to the front // of the formatted number ; N & D // and the decimal part if it exists ) ) // 26-05-2011, Martin Spanjaard, Trias DigitaalFormatN // 09-11-2013, Modified by Miguel Ãngel Fernández to allow natural numbers with decimals |
||
|
Martin Spanjaard, Trias Digitaal Nov 12, 2013 |
|
| My own version of this was the following, which I think is a bit more elegant. FormatAmount( amount ; decimalSeparator ) = Let( [ sign = Case( amount < 0 ; "-" ) ; amount = Abs( amount ) ; separator = Case( decimalSeparator = "." ; "." ; "," ) ; decimals = Right( "00" & ( Round( amount ; 2 ) * 100 ) ; 2 ) ; amount = FormatN( Int( amount ) ) ]; sign & amount & separator & decimals ) // If the decimalSeparator is empty, it's a comma by default // 26-05-2011, Martin Spanjaard, Trias Digitaal |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.