Custom number format #,##0.## yields extra '.' when formattingintegers

[carolinerioux]

Hello,

Using the following custom number format "#,##0.##" on a cell yields
the following:

1.23 => 1.23 (as expected)
1.20 => 1.2 (as expected)
1.00 => 1. (notice extra .)

This is using Excel 2003 or Excel 2007.

I looked at the Office Open XML standard, and the appropriate section
states (ECMA-376/Part4/3.8.31):

-----
#: Digit placeholder. This symbol follows the same rules as the 0
symbol. However, the application shall not display extra zeros when
the number typed has fewer digits on either side of the decimal than
there are # symbols in the format. For example, if the custom format
is #.##, and 8.9 is in the cell, the number 8.9 is displayed.
-----

I can see that it does not specify anything about the '.' in the case
where all decimal digits are '0', and so maybe the behaviour is
correct.

However it is not what I would expect. Would there be a number format
that I could specify that would do what I want (which is 2 digits of
precision unless there are zeros, and no extra '.')?

Thanks very much for your help,
Caroline

 

[Brad]

Am I missing something - why doesn't the general format work? (Don't need to
have the extra work)

 

[carolinerioux]

Thanks for your reply.

It would work except for the thousand separator. My example could have
been clearer:

1001.23 => 1,001.23 (as expected)
1001.20 => 1,001.2 (as expected)
1001.00 => 1,001. (notice extra .)

Caroline

 

[Dave Peterson]

That's the way excel works.

I don't think that you'll be able to find any solution in formatting.

If it's really important, you could use a helper column and a formula that
returns text.

Or you could use a worksheet event (_change or _calculate) that changes the
format of the cell.