# 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 Creative Solutions Incorporated http://www.creativesltns.com |

==========

FormatNumberAsText ( 1234567.1234 ; "€" ; " " ; "," ; 2 )

==========

€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, Matthews, NC 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, Matthews NC 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, Matthews, NC 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, Utrecht / The Netherlands 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, San Francisco 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, Melbourne Australia Apr 27, 2016 |
||

Much appreciated. Just what I needed to correctly format currency amount totals within a text calculation field. | ||

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. | ||

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