Brian Dunning's FileMaker Custom Functions

AddDollarFormat ( Number )

Turn unformatted number into dollars with commas rounded to nearest penny.

  Average rating: 4.2 (36 votes) Log in to vote

Jeremiah Small   Jeremiah Small - Show more from this author
Soliant Consulting, Inc.
https://www.soliantconsulting.com

Share on Facebook Share on Twitter

  Sample input:
AddDollarFormat ( 123456789 )
  Sample output:
$ 123,456,789.00

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

Note: On 10/5/2006, Micah Woods modified the function to support negative numbers. Thanks Micah.

Note: A typo was fixed in this function on 9/14/2005 which caused the original version to return the wrong value for 7 and 8 figure numbers. Thanks to Angie at Biscuit Technology for reporting the problem :)

This function takes an unformatted number and formats it with dollar sign, commas, and rounds to the nearest penny. It is useful if you need to display dollar results in a concatenated text field with other text. It only will format numbers up to 999,999,999.99. Numbers larger than that will be returned without commas. Of course if you need to format 1 billion dollars and higher, you can afford to have me extend the range if you can't figure out how to do it yourself :)

 

Comments

Brian Dunning   Brian Dunning, BrianDunning.com
Jan 19, 2009
This is better than mine because it inserts commas. (I guess that depends on whether you want commas.)
 
Tom Myers   Tom Myers, Seattle WA
May 20, 2009
Very nice. Can you add some variables for setting prices ( merchandising )

a) rounds to the nearest (5), then subtracts ($.01) or ($.05) to get $14.99 or $14.95

b) rounds to the nearest (10), then subtracts ($.01) or ($.05) to get $89.99 or $89.95
 
Eric Ridgley   Eric Ridgley, Fairlawn, OH
Jan 12, 2010
I've used this function and added:

1. Can pass a prefix
2. Can declare precision of rounding
3. Added formatting to 15 characters (trillions)

Where should I add the updated version?
 
Mike B.   Mike B., Columbus, OH
May 2, 2012
This is pretty useful. I removed the space after the dollar sign, changed it to round to the nearest whole number and took out the two decimal places to fit my needs. Thanks!
 
Fabián Montagno   Fabián Montagno, México, DF
May 21, 2012
Occam's Razor is the scripts to convert numbers to text. Thanks!
 
Andre   Andre, ev4FbdZUIu
Nov 27, 2015
Um, doesn't this just mean that the student buys the paper beorfe the first draft is due? And that you've probably edited a purchased paper?You can usually purchase different levels of work, on a sliding price scale. So the student buys a B paper, takes it to you, you discuss it and edit it together, and the kid saves the money and gets an A-. Now, you can usually get around this, but you have to keep track of the student step by step from the initial planning stages all the way to the final draft. Brainstorming, freewriting, outlining, drafting, etc. You can then have them write a reflection essay on the process itself. And that's all more work than most anyone can actually keep track of, as a teacher.(Also, be aware that your students are also having their friends and family edit i.e., rewrite their work for them.)My solution at the high school level is rather simple. Our curricula specifies that we have to assign one formal take-home essay and one in-class essay test every eight weeks. So I have students write the in-class essay test first. Then they have to revise and expand it (double the size) for their take-home essay, which they submit to a plagiarism-checking website.
 
Omelyan Burtnyk   Omelyan Burtnyk, Go To Logistics
Jun 26, 2016
much better like this:

Let ( [ S = Round ( Abs ( 123456789012.567 ) ; 2 ) ;
I = Int ( S ) ;
L = Length ( I ) ;
D = Right ( S; 2 );
Dec = If ( S <> I ; D ; "00" )
] ;

Case (S < 0 ; "-") & "$" &
Case (
L = 12 ; Left ( I ; 3 ) & "," & Middle ( I ; 4 ; 3 ) & "," & Middle ( I ; 7 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 11 ; Left ( I ; 2 ) & "," & Middle ( I ; 3 ; 3 ) & "," & Middle ( I ; 6 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 10 ; Left ( I ; 1 ) & "," & Middle ( I ; 2 ; 3 ) & "," & Middle ( I ; 5 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 9 ; Left ( I ; 3 ) & "," & Middle ( I ; 4 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 8 ; Left ( I ; 2 ) & "," & Middle ( I ; 3 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 7 ; Left ( I ; 1 ) & "," & Middle ( I ; 2 ; 3 ) & "," & Right ( I ; 3 ) ;
L = 6 ; Left ( I ; 3 ) & "," & Right ( I ; 3 ) ;
L = 5 ; Left ( I ; 2 ) & "," & Right ( I ; 3 ) ;
L = 4 ; Left ( I ; 1 ) & "," & Right ( I ; 3 ) ;
I
)
& "." & Dec
)
 
Omelyan Burtnyk   Omelyan Burtnyk, Go To Logistics
Jun 26, 2016
or like this:

Let ( [
S = Round ( Abs ( 123456789012.567 ) ; 2 ) ;
I = Int ( S ) ;
L = Length ( I ) ;
D = Right ( S; 2 );
Dec = If ( S <> I ; D ; "00" )
] ;

Case (S < 0 ; "-") & "$" &
Case (
L = 12 ; Left ( I ; 3 ) & " " & Middle ( I ; 4 ; 3 ) & " " & Middle ( I ; 7 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 11 ; Left ( I ; 2 ) & " " & Middle ( I ; 3 ; 3 ) & " " & Middle ( I ; 6 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 10 ; Left ( I ; 1 ) & " " & Middle ( I ; 2 ; 3 ) & " " & Middle ( I ; 5 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 9 ; Left ( I ; 3 ) & " " & Middle ( I ; 4 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 8 ; Left ( I ; 2 ) & " " & Middle ( I ; 3 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 7 ; Left ( I ; 1 ) & " " & Middle ( I ; 2 ; 3 ) & " " & Right ( I ; 3 ) ;
L = 6 ; Left ( I ; 3 ) & " " & Right ( I ; 3 ) ;
L = 5 ; Left ( I ; 2 ) & " " & Right ( I ; 3 ) ;
L = 4 ; Left ( I ; 1 ) & " " & Right ( I ; 3 ) ;
I
)
& "." & Dec
)
 
Omelyan Burtnyk   Omelyan Burtnyk, Go To Logistics
Jun 26, 2016
And are generally good in a real project, like that:

Let ( [
CU = Globals::currencySymbol;
DP = Globals::decimalPoint;
BP = Globals::brakePoint;
ND = Globals::num_of_Decimals;
S = Round ( Abs ( Table::Field ) ; ND ) ;
I = Int ( S ) ;
L = Length ( I ) ;
D = Right ( S; ND );
Dec = If ( S <> I ; D ; "00" )
] ;

Case (S < 0 ; "-") & CU &
Case (
L = 12 ; Left ( I ; 3 ) & BP & Middle ( I ; 4 ; 3 ) & BP & Middle ( I ; 7 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 11 ; Left ( I ; 2 ) & BP & Middle ( I ; 3 ; 3 ) & BP & Middle ( I ; 6 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 10 ; Left ( I ; 1 ) & BP & Middle ( I ; 2 ; 3 ) & BP & Middle ( I ; 5 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 9 ; Left ( I ; 3 ) & BP & Middle ( I ; 4 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 8 ; Left ( I ; 2 ) & BP & Middle ( I ; 3 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 7 ; Left ( I ; 1 ) & BP & Middle ( I ; 2 ; 3 ) & BP & Right ( I ; 3 ) ;
L = 6 ; Left ( I ; 3 ) & BP & Right ( I ; 3 ) ;
L = 5 ; Left ( I ; 2 ) & BP & Right ( I ; 3 ) ;
L = 4 ; Left ( I ; 1 ) & BP & Right ( I ; 3 ) ;
I
)
& DP & Dec
)
 
Rene d'Auteuil   Rene d'Auteuil, Montreal
Feb 14, 2017
I prefer something like this...
//AddDollarFormat(number_;return;separator)
//ex. AddDollarFormat(123456789;"";"") return 123,456,789

Let(
[
n=number_;
l=Length(n);
r= Right(n;3);
s= Left(n;l-3);
j=Ceiling(l/3);
return=r& separator & return

];
Case(j=1;return;AddDollarFormat(s;return;separator))
)
 
Rene Dauteuil   Rene Dauteuil, Montreal
Aug 3, 2018
//AddDollarFormat(number_;return;separator;mod)
//AddDollarFormat(1022.2;"";" ";"") return 1 022.20
Let(
[
n=number_;
i=Int(n);
m=Mod(n;1);
c=Case(not Cents;Case(m=0;Left(10/100;1) & "00";Left(m & "0";3));Cents);


l=Length(i);
r= Right(i;3);
s= Left(i;l-3);
j=Ceiling(l/3);
return=separator & r & return

];
Case(j=1;Right(" " & return;12) & c;AddDollarFormat(s;return ;separator;c) )
)
 
 
Aug 18, 2022
While (
[
roundedNumber = Round ( Abs ( number ) ; 2 ) ;
integer = Int ( roundedNumber ) ;
decimal = Left ( Filter ( GetAsText ( number - integer ) ; "1234567890" ) & "00" ; 2 ) ;
currency = ""

] ;
integer > 100 ;
[
string = GetAsText ( integer ) ;
currency = "," & Right ( string ; 3 ) & currency ;
integer = GetAsNumber ( Left ( string ; Length ( string ) - 3 ) )
] ;
Case ( number < 0 ; "-" ; "" ) & "$" & integer & currency & "." & decimal
)

returns the number formatted no matter how big the number
 
 
Aug 18, 2022
never mind. breaks at 100000000
 

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 20 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: