# Format Calculation Field w/ different Data Types

This topic is 7080 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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?

Regards,

Jason

##### Share on other sites

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?

Regards,

Jason

##### Share on other sites

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)) &

##### Share on other sites

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

##### Share on other sites

What have you done to it? It breaks at 10 millions and at 100 millions.

(and you are doing a double Abs() function.)

##### Share on other sites

I don't know...

Will have to check this back.

##### Share on other sites

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

)

##### Share on other sites

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

##### Share on other sites

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 !

##### Share on other sites

Actually, not. I had a problem with e.g. -0.001. I have fixed it and edited the previous post.

##### Share on other sites

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!

##### Share on other sites

The thanks should go to DJ (Djukic Goran) for coming up with the idea. I only cleaned it up a bit for ver.7.

##### Share on other sites

This topic is 7080 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account