Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 846
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Custom number format #,##0.## yields extra '.' when formattingintegers

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.

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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving custom number format to NUMBER Doug Boufford Setting up and Configuration of Excel 3 July 23rd 07 11:58 PM
Custom Autofilter yields no results rjejyork Excel Discussion (Misc queries) 4 July 20th 06 09:10 PM
how do I add phone number format as a permanent custom format? frustratedagain Excel Discussion (Misc queries) 3 February 4th 06 04:52 AM
Format a cell with a custom number format Armor Excel Worksheet Functions 4 January 30th 06 12:25 AM
Custom number format always defaults last number to 0. scubadave Excel Discussion (Misc queries) 2 June 15th 05 10:20 PM


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"