Excel number format thousand separator not working as expected

Question

Here's the situationMy 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 not work as expected.Here is the number format I am using: #,##0. But it adds an extraneous comma on smaller numbers as below (it's fine for thousands):,5,50,5005,000I get the same result with ANY separator I use, EXCEPT space, which works fineAnd, here's the weird thing. If I go into Excel advanced settings and override the number formats to use commas, it works fine again. (But this is NOT a solution, as I want Excel mostly to use spaces, but for me to be able to override this with formats when needed)I have done a test, and if I change my system separator to commas, and then use a number format with anything other than commas, I get the same problem! So it seems like somehow, for everything other than my default thousand separator, Excel always puts the separator in front of smaller numbers, where it is not needed.FYI I get the same result if use the TEXT function too.I have the same problem on two machines, one on Win 10, the other on Win 11, so I doubt this is an install issue etc needing repair. FYI I am using Microfosft365 on an Enterprise E3 account.Anyone else seen this before, or can help? Am I doing something obviously wrong here?

hansvogelaar · Answer

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:
[&lt;=-1000]-0,000;[&gt;=1000]0,000;0
Note that this format will only insert&nbsp;one comma, so it is correct up to 999,999.

If you only need to do this for non-negative numbers, you can use
[&gt;=1000000]0,000,000;[&gt;=1000]0,000;0
This will work correctly up to 999,999,999: