Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Millions (2000 SR2)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Millions (2000 SR2)

    Use this custom format:

    #.0,,
    (the comma's do the trick)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Millions (2000 SR2)

    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,,
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Format Millions (2000 SR2)

    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
    [MS MVP - Word]

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Millions (2000 SR2)

    Seems to work for me when I use:

    ????.????,,
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Millions (2000 SR2)

    Great! Thanks to you both.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •