Format Millions (2000 SR2)

Topic

New Reply

In Excel, I made a custom format so that a number entered as 1000000 would display as 1.0. Now it’s a year or so later, and I can’t figure out how to do it again. Please help.

thanks

Reply | Quote

Replies

Use this custom format:

#.0,,
(the comma’s do the trick)

Reply | Quote

Thanks. I did figure it out, but it gave rise to a related formatting question.

I would like to display millions and center on the decimal points.

Normally, you can center on the decimals with the custom format ???.???. When I combine the two in the custom format box entering: 0.0,, ???.??? strange things start to happen. The cells are aligned at the decimal, but there is another decimal point to the right of the last zero and some additional space. When I recheck the formatting, what’s entered in the custom format box has changed to: 0.0 ??.??. Spreadsheet example is attached. 1st column formatting 0.0,, ???.??? . 2nd column formatting 0.0,,

Reply | Quote

Hi Cathy,

The extra decimal point is caused by your ‘??.??’ formatting. I think you can get the result you’re after with something like:
#,##0.0,,aaaaaaaaa
where the ‘a’s are however many spaces you want to pad the right side of the number with.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Seems to work for me when I use:

????.????,,

Reply | Quote

Great! Thanks to you both.

Reply | Quote