Strang error with custom formatting

[daniel.tellberg]

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!?

[63falcondude]

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

[ben_hensel]

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.