+ Reply to Thread
Results 1 to 3 of 3

Strang error with custom formatting

  1. #1
    Registered User
    Join Date
    05-16-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    365, version 1904
    Posts
    1

    Strang error with custom formatting

    Hi,

    Iäm running excel on Windows set to English (Great Britain) region settings.
    Standard thousand separator is ","

    I'm creating a financial document presenting an income statement with english and swedish formatting side by side.
    Since English is my setting, iäm trying to use custom formatting to generate the swqeidh number format.

    Thousand separator skuld be " "; space
    There should be no decimals.

    Zero should be just "-"

    I'm currently using the following custom formatting:

    # ##0;-# ##0;"-"

    Works just fine, besides negative amounts less than a thousand.

    -2000 => "-2 000"
    -200 => "- 200"

    There's a space between "-" and the amount when the amount is beneeth a thousand.
    Why!?

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,379

    Re: Strang error with custom formatting

    This is happening because -# ##0 tells Excel to always put a space one character to the left of the hundreds place.
    The # allows the characters to be omitted if the number is not large enough but the space will always be there with your custom formatting.

    I believe that you would have to change the thousands separator in Excel settings:
    https://support.office.com/en-us/art...5-aebb9837bd1e

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,949

    Re: Strang error with custom formatting

    Because it's not being treated as a thousands separator exactly, it's putting a space on front of the hundreds, and then going into the thousands. So it's treating "##0" as " ##0" if that makes sense. It's visible in the negative domain because of the negative sign; making it +# ##0;-# ##0;"-" would exhibit the same behavior.

    You could cheat on this a little bit by left-aligning the negative sign like # ##0;-* # ##0;"-" but I dunno if that works for you.

    Also,
    Note that this won't be putting a separator between thousands and millions (digit six and seven). I dunno if that's a problem for you or not.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1