Search code examples
vbaexcel

Set standard number format with thousand separator, 2 decimals and minus sign for negative numbers using VBA?

I've seen the question asked before on stackoverflow, how to get normal number format with thousand separator and 2 decimals. The answer was to set:

    rng.NumberFormat = "##0.00"

But this is incomplete, because, at least on my computer, I don't get any space separator between millions and thousands. So I have changed that to:

    rng.NumberFormat = "### ### ##0.00"

But this is again incomplete, because for some reason negative numbers were formatted to look like they have a space between the minus sign and the number. See below:

- 12.4

So, there are some things left to do to arrive at Excels "built-in" "format as number" formats. Additionally the formatting that I apply though VBA is described as Custom by Excel.

Is there any way to set the format to be the standard built in format as number with thousand separators, 2 decimals and minus signs for negative numbers?

I'm looking for something like:

    rng.NumberFormat = "Number, 2, minus"
Solution 
  • rng.NumberFormat = "# ##0.00:-# ##0.00"

    You put the format for positive numbers before : and the format for negative after. You don't need to put hundreds of # signs in the format, just enough to show what the 1000's separator is.