Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom number format #,##0.## yields extra '.' when formattingintegers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom number format #,##0.## yields extra '.' when formatting int
Am I missing something - why doesn't the general format work? (Don't need to
have the extra work) -- Wag more, bark less " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom number format #,##0.## yields extra '.' when formattingint
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 On May 12, 3:01 pm, Brad wrote: Am I missing something - why doesn't the general format work? (Don't need to have the extra work) -- Wag more, bark less " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
Custom Autofilter yields no results | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) |