CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

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"

Upvotes: 2

Views: 22179

Answers (2)

Fleischbender
Fleischbender

Reputation: 21

So, this is an old post. But people may still have this problem, and I have a workaround. This solution works nicely for contained data sets, such as a table or a chart you want thousand separator for.

The issue is (at least how it is for me, in continental Europe) that the system thousand separator is " ", i.e a space. Hence, the number format for thousand separator ought to be "# ###" or equivalently "# ##0" (or "# ##0,00" if you want 2 decimals with comma as decimal separator). Indeed, if you make a chart and ask Excel to format axis as numbers with thousand separator, Excel gives you this number format: "# ##0".

However, if you use "# ##0" or "# ###" to make your own number format, Excel only gives you a separator between million and thousand. E.g. if your numbers are a billion and ten thousand, the chart axis will show 1000000 000 and 10 000. Excel will not give you the desired 1 000 000 000 and 10 000.

Note: The issue is of course the same if you want to use other thousand separators than space, say, comma or punctuation.

This is my solution to this problem for the case without decimals (just add the number of decimals you want like you normally would):

Dim n As Long, i As Long, MyFormat As String, MySeparator as String, MyData As Range, x as double

Set MyData = 'Range containing your data
MySeparator = 'Your prefered thousand separator

'Number of separators you need for the largest ABS-value in your dataset
x=Application.WorksheetFunction.Max(MyData) 'Largest number
x=Application.WorksheetFunction.Max(x,-Application.WorksheetFunction.Min(MyData)) 'Largest ABS-value
n = Int((Len(CStr(Int(x))) - 1) / 3) 'Number of separators required

MyFormat = "###" 'Initial value

For i = 1 To n
   MyFormat = "###" & MySeparator & MyFormat
Next

Upvotes: -1

simon at rcl
simon at rcl

Reputation: 7344

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.

Upvotes: 2

Related Questions