Format number to dollar and cents

building reports webintelligence
1

I have a column with number and would like to format into dollar and cents. Last two digits are always cents. for example 345600 = 3456 $ 00 cents. 4567=45$ 67 cents.

pkommidi (BOB member since 2006-03-10)

2

Well, have you looked at the currency formats :? ?

Mak 1 :uk: (BOB member since 2005-01-06)

3

This field in database is a number field and we need to format dollars and cents. When i use the format, it always adds the 00 as cents.

pkommidi (BOB member since 2006-03-10)

4

Well, try creating a custom format?

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hi,

If nothing else then try a formula like this:

=Substr(FormatNumber([number];"0");1;Length(FormatNumber([number];"0"))-2) + "$ " + Right(FormatNumber([number];"0");2) + " cents"

However, please note that the result is a string. It’s not a formatted number. It’s a string.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

If we need to use them calculation, we better of converting them into numbers. Is it best to load into database column.

pkommidi (BOB member since 2006-03-10)

7

How about this output?

3456$.00
45$.67

I got this by applying, this format:-

0$.00

Then creating a “precision” variable / divisor of 100.
Doing anything else will no longer make them numbers, as in Mareks example.

Mak 1 :uk: (BOB member since 2005-01-06)

8

When i format any number last two digits should be cents and rest should be dollars. For example 3456= 34.56 , 892=8.92 55000=550.00

pkommidi (BOB member since 2006-03-10)

9

You need to divide your value by 100 and store it in a variable. Then apply the currency format on it.

bbee (BOB member since 2010-03-09)

10

Then divide the number by 100.

Or am I missing something? :?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

11

Yep :), if you divide the number by 100 and then apply the custom format, I suggested, then you will get the results that I described.

You can also create the variable as bbee described, then apply the format.

Obviously, you can’t have the “cents”, in words part for this to continue to be a number, as in your example, Marek.

Mak 1 :uk: (BOB member since 2005-01-06)