Custom currency format

[Former Member]

Hi All,

I have currency field which needs to be formatted as below :

- The last two positions of a dollar amount field are considered as cents and no decimal points.(ie: 100 = 1.00)

- Trailing zeros are required for amount fields in order to properly indicate cents (i.e. 100 = 1.00 or 10000 = 100.00).

- No dollar sign is required

Ex:

- Amount 1 : $9375.00 should be display as 937500

- Amount 2 : $1037.32 should be display as 103732

I have tried this custom format "###0.00" which only removes the $ sign but unable to include last two digits as cents without dot symbol.

Any suggestions would be appreciated.

Thanks,

Rameez Shaikh

[former_member190895 · Accepted solution]

Will Replace function will work in your case. Just replace dot with blank and then trim the figure

[former_member201488 · Accepted solution]

You can't use string operators on numeric fields, and the decimal point is part of the formatting, not the value itself - hence you couldn't "replace" it anyway -  you format to a round number.

You need to convert the number to a string first:

=Replace(""+FormatNumber([Number];"0.00");".";"")

The problem with this is that you then lose all calculation capability; therefore, I would advise to apply the above as an alerter or conditional formatting - that way the underlying value is retained, but the presentation is as required.

HTH

NMG