FormatNumberAsText ( number ; currency ; separator ; decimal ; precision )
Number to text, padded/rounded to a specified number of decimal places, user specified decimal and thousands separators, optionally displayed currency symbol
Average rating: 3.9 (61 votes) Log in to vote
|
Geoffrey Gerhard - Show more from this author
Creative Solutions Incorporated https://creativesltns.com |
Sample input:
FormatNumberAsText ( 12345678901234567.1234 ; "$" ; "," ; "." ; 2 )
==========
FormatNumberAsText ( 1234567.1234 ; "€" ; " " ; "," ; 2 )
==========
FormatNumberAsText ( 1234567.1234 ; "€" ; " " ; "," ; 2 )
Sample output:
$12,345,678,901,234,567.12
==========
€1 234 567,12
==========
€1 234 567,12
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
This function formats numbers for use in text strings or calculations with a text result. It's especially useful when there are multiple numbers in a text string and at least one must be displayed in a different format than the others. It works on numbers of any length, and produces its result without recursion.
Separator and Currency parameters are optional.
Decimal parameter defaults to "." or "," (based on localization) unless rounding is <= 0, in which case it's omitted.
Precision defaults to 2 if parameter is not a digit or is unspecified.
UPDATED 11.20.2009
Added " + 0" to declaration of "n" variable to deal with cases where the number parameter is null or empty.
UPDTED 09.10.2015
Fixed a bug that resulted in malformed numbers ( like "€10..05,05" ) in some European FMP 12 (and higher) files.
Modified Decimal to use the correct integer/fraction separator [ "." or "," ] for the file's region when the decimal parameter is null.
Comments
 |
TnIan, Doofusburg May 4, 2011 |
|
| Awesome! That's exactly what I needed. Thanks! | ||
|
Chris, Temecula Jan 3, 2012 |
|
| I am using your wonderful calculation but made a mistake somewhere. When i enter an amount i get a number of zeros after the desired amount: $175.2300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 Any way you can tell me where i messed up? Let ( [ t = Int ( GetAsNumber ( amount_paid ) ) ; prec = Case ( Length ( t ) ; t ; 2 ) ; dec = Case ( Length ( "." ) ; "." ; "." ) ; n = Round ( amount_paid + 0 ; prec ) ; sgn = Case ( n < 0 ; "-" ) ; n = Abs ( n ); x = NumToJText ( n ; 1 ; 0 ) ; y = NumToJText ( n * 10^prec ; 0 ; 0 ) ; z = Position ( x ; "." ; 1 ; 1 ) ] ; sgn & "$" & Substitute ( Case ( z = 1 ; 0 ; z ; Left ( x ; z - 1 ) ; x ) ; [ "," ; "." ] ) & Case ( prec > 0 ; dec & Right ( 10^prec & y ; prec ) ) ) Thanks |
||
|
Geoffrey Gerhard, Creative Solutions Incorporated Jan 5, 2012 |
|
| Chris: The value of "t" is the precision, so you're rounding to a number of decimal places equal to the integer value of the number you're formatting. Geoff |
||
|
Al, Valais, Switzerland Aug 24, 2012 |
|
| Fabulous, thank you. Just what I've been looking for. In cases where my number field is empty I'd like to be able to strip out the currency symbol in the output, but I can't work out how to amend the calc to do this. Eg */ Let ( [ t = Int ( GetAsNumber ( 2) ) ; prec = Case ( Length ( t ) ; t ; 2 ) ; dec = Case ( Length ( "." ) ; "." ; "." ) ; n = Round ( Flights Total + 0 ; prec ) ; sgn = Case ( n < 0 ; "-" ) ; n = Abs ( n ); x = NumToJText ( n ; 1 ; 0 ) ; y = NumToJText ( n * 10^prec ; 0 ; 0 ) ; z = Position ( x ; "." ; 1 ; 1 ) ] ; sgn & Currency & Substitute ( Case ( z = 1 ; 0 ; z ; Left ( x ; z - 1 ) ; x ) ; [ "," ; "," ] ) & Case ( Flights Total ≤ 0 ; ""; prec > 0 ; dec & Right ( 10^prec & y ; prec ) ) ) Any ideas much appreciated. Thanks million |
||
|
Geoffrey Gerhard, Creative Solutions Incorporated Aug 24, 2012 |
|
| Changing the line... sgn & Currency & ...to... sgn & Case ( Length ( number ) ; Currency ) & ...should do it. |
||
|
Al, Valais, Switzerland Aug 25, 2012 |
|
| Yep, that's done it. Thanks so much for this, I've been trying to create this for, well, years! I finally have a multi-currency invoice system that only shows items with a value. Hugely appreciated! |
||
|
Roland, Berlin Mar 10, 2014 |
|
| Hiho over there, tried this function but found out, that it doesn't work with the language systems where the Standard decimal is "," and the thousand sep is "." E.g. if I have 654,21 EUR it converts to EUR654..21,21 Too weird for me to find a soultion, please help... Thanks, Roland |
||
|
Geoffrey Gerhard, Creative Solutions Incorporated Mar 10, 2014 |
|
| It has worked for others using the notation you described, so i wonder what's different in your use case.... If you're willing to put together a sample file, I'd be glad to look at it--even if just to satisfy my own curiosity. Just two fields would be needed--a number field for the raw input and a calc field that shows what values (if any) you've assigned for each parameter. The link to my web site on the CF page shows my email address. |
||
|
Barney, Cornwall May 5, 2014 |
|
| Very handy function. Could it work with a repeating text field (i.e. the output of an ExecuteSQL call)? Thanks, Barney. |
||
|
Nihm, NY, NY Jul 25, 2014 |
|
| Thanks! | ||
|
Chris, London Dec 18, 2014 |
|
| Works a treat. Thanks Geoffrey. | ||
|
Brian, Eden Prairie, MN, USA Mar 25, 2015 |
|
| Geoffrey, Thank you! Thank you! Great custom function! My headache is diminishing! |
||
 |
Ernst, Froodware Sep 7, 2015 |
|
| Hi ! Thanks for sharing, but unfortunately I ran into the same problem as Roland from Berlin; I'll put an example together and mail it. Best, Ernst info <at> clineco.nl |
||
 |
Mike Dec 7, 2015 |
|
| Thank you! | ||
 |
Robert, Luxembourg Europe Dec 14, 2015 |
|
| Gracias... | ||
|
Pep Espunyes, Catalunya Mar 2, 2016 |
|
| Great! | ||
|
Michelle Preston, Mill Bay BC Mar 21, 2016 |
|
| Thanks! | ||
 |
Joe, iSolutions Apr 22, 2016 |
|
| Thank you so much! This is a great and necessary function. Any idea why FileMaker hasn't provided this as a standard feature? | ||
 |
Ian, Centreforce Apr 27, 2016 |
|
| Much appreciated. Just what I needed to correctly format currency amount totals within a text calculation field. | ||
 |
John, Tightrope Jun 30, 2016 |
|
| This function saved the day!!! Shame on filemaker for not including it. | ||
|
Seamus Berkeley, Taos Oct 17, 2016 |
|
| Thanks so much for this function—works! | ||
|
Luis Sardinha, Gloucester, UK Jul 14, 2017 |
|
| Very nice custom function. Very useful. Works like a charm. Cheers! |
||
|
Alistair Hay, Harrogate, UK Apr 10, 2018 |
|
| Nice one. Thanks very much |
||
|
Chris, Whitianga, NZ Apr 28, 2018 |
|
| Brilliant! Many thanks. | ||
 |
Ole Kristian Ek Hornnes, ProgramDesign Oct 29, 2018 |
|
| Works excellent - and it is also very fast. | ||
 |
Sean, Private Dec 20, 2021 |
|
| Fantastic Function!! Thank you. I am unsure how to skip using a currency symbol though. No matter what I put in there I get my system default currency. I cannot change it to $ or leave it blank. The description does say Currency is Optional. I've tried setting it as "", 0, 1, space etc. No change. MY FMP ver is 19, Windows and is up to date. EXAMPLE: FormatNumberAsText ( Sum ( Director_Shareholder::Shares ) ;0;","; 0; 0) & " Shares" (I am trying to just format using thousands separator. Calculation is used in a Menu Button) | ||
|
Geoffrey Gerhard, Creative Solutions Incorporated Dec 24, 2021 |
|
| @Sean: I copy/pasted your example in my Data Viewer, replacing your number parameter and currency parameters like this... FormatNumberAsText ( 1234.99 ;"";","; 0; 0) & " Shares" ...and got "1,235 Shares" as the result. Paste your original expression in your Data Viewer, replace the 0 currency parameter value with "". If it produces the expected result (no currency symbol) in the Data Viewer, the problem is coming from field or layout object formatting. |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.