March 21, 200520 yr Hello, I have a calculation field that incorportates both Text and Numbers. I would like to format the numbers to have thousands seperator, but the calculation result has to be Text; given that, is there anyway to format the numeric values within the calculation? I tried to format the field in the Layout view by clicking on the merge field and then going Format->Number, but it didn't do anything (which makes sense since the result is being formatted as Text). Is there a function that I can use line by line within the calculation? Thank you for your assistance. Regards, Jason
March 21, 200520 yr Author Hello, I have a calculation field that incorportates both Text and Numbers. I would like to format the numbers to have thousands seperator, but the calculation result has to be Text; given that, is there anyway to format the numeric values within the calculation? I tried to format the field in the Layout view by clicking on the merge field and then going Format->Number, but it didn't do anything (which makes sense since the result is being formatted as Text). Is there a function that I can use line by line within the calculation? Thank you for your assistance. Regards, Jason
March 22, 200520 yr The only way that I know of is to create a calculation to parse the number and insert the commas. I am sure there is a cleaner and shorter way to do this, but this still works: If the field name is: testnum And the number is: 1256743.20 And the desired result is: 1,256,743.20 USE: // Millions if( length(int(testnum)) > 9, right(left(testnum, length(int(testnum)) - 6),3), left(testnum, length(int(testnum)) - 6) ) & // Millions Comma if(length(int(testnum)) >6 , "," , "") & // Thousands if( length(int(testnum)) > 6, right(left(testnum, length(int(testnum)) - 3),3), left(testnum, length(int(testnum)) - 3) ) & // Thousands Comma if(length(int(testnum)) >3 , "," , "") & // Hundreds right(int(testnum),3) & // Cents case( length(testnum - int(testnum)) = 3, (testnum - int(testnum)) , length(testnum - int(testnum)) = 2, (testnum - int(testnum)) &
March 22, 200520 yr Here's one I adapted from a former post here on the Forums to handle different formatting according to the country specs. May be it may help. PriceFormat.fp7.zip
March 22, 200520 yr What have you done to it? It breaks at 10 millions and at 100 millions. (and you are doing a double Abs() function.)
March 22, 200520 yr Try this: Let ( [ // USER DEFINED: input = numberfield ; precision = 2 ; currency = "$" ; separator = "," ; decPoint = "." ; // DO NOT MODIFY FROM THIS POINT ON inputRound = Round ( input ; precision ) ; Amt = Abs (inputRound ) ; Dollars = Int ( Amt ) ] ; // PREFIX Case ( inputRound < 0 ; "-" ) & currency & // BILLIONS Choose ( Dollars < 10 ^ 9 ; Right ( Div ( Dollars ; 10 ^ 9 ) ; 3 ) & separator ) & // MILLIONS Choose ( Dollars < 10 ^ 6 ; Right ( Div ( Dollars ; 10 ^ 6 ) ; 3 ) & separator ) & // THOUSANDS Choose ( Dollars < 10 ^ 3 ; Right ( Div ( Dollars ; 10 ^ 3 ) ; 3 ) & separator ) & // UNITS Right ( Dollars ; 3 ) & // FRACTION Choose ( precision < 1 ; decPoint & Right ( 10 ^ precision & Amt * ( 10 ^ precision ) ; precision ) ) )
March 22, 200520 yr Author This worked, thanks! I'm just going to insert the commas in the Grand Total because it would be quite tedious to stick that calculation for each of the line items. Thanks again! Regards, Jason
March 23, 200520 yr Thanks. I messed with the Abs( ) part in my calc. However, what was working with FM5 isn't anymore here. Yours does the job, surely !
March 23, 200520 yr Actually, not. I had a problem with e.g. -0.001. I have fixed it and edited the previous post.
March 23, 200520 yr Ack! I accidently omitted the line for displaying the negative symbol. I probably should have just copied one of my old calcs that I use instead of attempting to make one on the fly. It would have been a lot more complete, though a bit longer as my skills have increased as more time passes. No matter! Comment has done a far superior job in any case. As I suspected, there is a far better and shorter way to accomplish parsing a number to insert commas. I will be making use of this in my conversions for certain. Thanks Comment!
March 23, 200520 yr The thanks should go to DJ (Djukic Goran) for coming up with the idea. I only cleaned it up a bit for ver.7.
Create an account or sign in to comment