Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Number Format (97+/All)

    In Australia there is a number formatting standard that requires thousands to be separated by a space instead of the " , " (comma). I had tried customising a number format that includes commas as the separators but never achieved what was necessary. Then a light bulb went on and I decided that the Windows Settings needed to be changed (Control Panel, Regional Settings, navigate to 'Digit Grouping Symbol' and replace the comma with a space). (See attached)

    Then, back in MS Excel in Format Cells, choose to use a number format that has the option 'Use 1000 Separator ( )'. Note the space in the brackets.)

    This has been great for most numbers but has a limitation. It will display correctly for two or three decimal places but does not display beyond that (e.g. 1 234 567.00 but 1 234 567.123456).

    Any suggestions?
    Attached Images Attached Images

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Number Format (97+/All)

    Separators only work on the integers, not the fractions; this is so with commas or any separator; they don't show on the right side of the decimal place.

    Try the following work-around where the spaces are ALT-NUMPAD entered as Alt-255:

    =TEXT(cellref_or_calculation,"###
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Number Format (97+/All)

    Thank you John for your quick reply.

    I do need to use these numbers as numbers though. Therefore for presentation your response is OK but I can't perform calculations on them. Or is there another way?

    Leigh

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Number Format (97+/All)

    BTW, who uses this unusual standard in Oz?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Number Format (97+/All)

    Not easily, as =VALUE() will not directly recognise the format. You can use
    =VALUE(SUBSTITUTE(cell_ref,"
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Number Format (97+/All)

    Thanks for the suggestions. I will try it out later today.

    Re: Who uses the format? A government department that produces certificates prefers the 'correct' legal presentation. FYI, an Australian Standard was introduced in 1975 (when we changed to SI and the metric system) but like most of us we accept the general concensus that the commas are OK (especially as Microsoft has not been alerted to the requirement that we should adopt the standard in all produced material. Can't blame MS when we as a group are tardy in adopting the change.)

    Thanks again.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Number Format (97+/All)

    FYI: In Belgium, we use the comma as decimal separator and (often) the space as thousand separator. Just to make it more complex, especially if you want to write some international VBA stuff; you always have to take the Application.International(...) command as part of your programme.

Posting Permissions

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