How to format without decimal point unless significant digits?

[Jennifer Murphy]

Is there a custom format string that will format a number so that the
decimal point is displayed *only* if there are non-zero digits to the
right?

I tried #,##0.####, which works great except for integers.

"12345" formats as "12,345."

I'd like to get

"12,345"

But I want to get the decimal point and any non-zero digits to the
right of the decimal point. Here are some examples:

Before After
0.123456 0.1235
5.34 5.23
25 25 /* no decimal point */
1,234.56789 1,234.5679

 

[joeu2004]

Is there a custom format string that will format
a number so that the decimal point is displayed
*only* if there are non-zero digits to the right?

It might be helpful to know what version of Excel you are using, i.e.
XL2003, XL2007, XL2010 or whatever.

I am told that XL2010 (and XL2007?) has added Conditional Formatting
capability that might apply here. In particular, you might be able to
specify the conditional formula =INT(A1)=A1 with the conditional format of
Numbers with zero decimal places.

I do not have XL2010 or XL2007, so I cannot say for sure. This capability
does not exist in XL2003 and earlier.

 

[Jennifer Murphy]

It might be helpful to know what version of Excel you are using, i.e.
XL2003, XL2007, XL2010 or whatever.

I am told that XL2010 (and XL2007?) has added Conditional Formatting
capability that might apply here. In particular, you might be able to
specify the conditional formula =INT(A1)=A1 with the conditional format of
Numbers with zero decimal places.

I do not have XL2010 or XL2007, so I cannot say for sure. This capability
does not exist in XL2003 and earlier.

I am using Excel 2007.

 

[joeu2004]

[....]

I am told that XL2010 (and XL2007?) has added Conditional
Formatting capability that might apply here. In particular,
you might be able to specify the conditional formula
=INT(A1)=A1 with the conditional format of Numbers with
zero decimal places.

[....]
I am using Excel 2007.

So did you try what I suggested?

As I said, I do not have XL2007, so I cannot experiment with this myself.

If that does not work and you do not get a satisfactory response in a timely
manner, post your inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.

It's not that I like that forum. It's just that MS has ceased to support
the Usenet newsgroups; see
http://www.microsoft.com/communities/newsgroups/default.mspx. Hence,
participation here is limited to the sites that share a common newsgroup
mirror, which is no longer centralized at MS.