Forum Discussion
Excel number format thousand separator not working as expected
Here's the situation My system thousand separator is [space], but in some situations I wish to use comma as the separator. But, when I set the number format for this on the cells required it does ...
As you have found, Excel will not treat another character correctly as thousands separator. As a workaround, you can use the following custom number format:
[<=-1000]-0,000;[>=1000]0,000;0
Note that this format will only insert one comma, so it is correct up to 999,999.
If you only need to do this for non-negative numbers, you can use
[>=1000000]0,000,000;[>=1000]0,000;0
This will work correctly up to 999,999,999:
Hey Hans, that's a great workaround, thanks, didn't know you could do that with number formats!
(And how crazy the normal approach simply doesn't work, seems mad this can't be fixed by MS - as if it doesn't work, why offer it?!)