Number formatting in Excel

Not Mike · #1 08-14-2002, 10:32 AM

I hate trying to format numbers in Excel.... I tried following the "custom format" but some of those are nearly possible to understand...

Here's what I'm trying to do:

I want to have a number format that is a "-" when it's zero, but also allows me to center my number in a cell... for some idiotic reason, the "-" is already centered, but the "number" gets right justified (and won't allow you to change the position of either!)....

Anyone know how to work around this??

Cho Da · #2 08-14-2002, 10:33 AM

If you want the number centered, set the alignment to center.

Not Mike · #3 08-14-2002, 10:53 AM

Quote:

Originally Posted by Puck

If you want the number centered, set the alignment to center.

This doesn't work in certain formats (for instance the "," format).... at least it doesn't in Excel 97....

Pseudolus · #4 08-14-2002, 10:59 AM

NM - Try

Code:

_( #,##0.00_)

. I don't know why, but it seems to work. (I started with one of the existing "Custom" codes and deleted the asterisk.)

Pseudolus · #5 08-14-2002, 11:03 AM

That one seems to get rid of the "-" for zero. Here's the extended dance remix to put that feature back:

Code:

_( #,##0_);_( (#,##0);_( &quot;-&quot;_);_(@_)

I have no idea what the last segment means, but it seems to work.

Not Mike · #6 08-14-2002, 11:08 AM

Pseudolus, thanks! I tried fiddling a bit, but didn't think to just remove the "*"....

Cho Da · #7 08-14-2002, 11:13 AM

Quote:

Originally Posted by Not Mike

Quote:

Originally Posted by Puck

If you want the number centered, set the alignment to center.

This doesn't work in certain formats (for instance the "," format).... at least it doesn't in Excel 97....

If you just want comma centered use:

Code:

0,000;(0,000);&quot;-&quot;

The * pads stuff out. You don't need the _) if you are centering anyway.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode