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 - Show more from this author
Soliant Consulting, Inc. https://www.soliantconsulting.com |
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, 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, 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, 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., 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, México, DF May 21, 2012 |
|
| Occam's Razor is the scripts to convert numbers to text. Thanks! | ||
|
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, 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, 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, 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, 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, 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 | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.