creating custom format appears to change locale

[stuck]

Windows 7, system locale is UK. Excel 2010 everything is English but...

Open Excel 2010, create a blank workbook
Select cell A1
Home tab, Number group, dropdown the Number format options
Select the bottom option, i.e. 'More number...'
Select Category Special and do nothing other than note that the Locale is English (U.K.)
Select Category 'Custom'
Select a Type say 0 (but I've tried several, it doesn't seem to mater which one you choose) and edit it to 000, i.e. I want numbers in cell A1 that are less than 100 to be preceded with leading one/two zeros
OK out of the dialog

The Number format dropdown then says 'Special' and when the dropdown is selected the date options are shown in Chinese. When I click on 'More number...' the left hand pane of the dialog box has 'Special' selected and on the right the Type: are all in Chinese and the Locale (location): is set to Chinese (Taiwan).

Once the locale is messed up like this I don't seem to be able to get to the Custom options to default to English UK



Ken

[Rudi]

Does the same in Excel 2013 ??
Any I was originally on South Africa Locale

1.jpg

2.jpg

[Rudi]

It seems as if the 000 format is a match to what Excel uses for one of its Chinese formats.
If you type 00000 then you will notice that Excel changes to an American locale since 5 zeros is part of their custom formats.

[stuck]

Yes, that sounds from a geeky coder's perspective 'logical' but not from a real world user point of view.

Oh well, think this takes the bizarre Excel thing counter to #21587458

Ken

[Rudi]

It is a bit of a "buggy" scenario me thinks... It's certainly something that should be cleared up by Microsoft!!

[stuck]

OK, armed with your deductions, further Googling suggested a workaround. I've tested it and it works.

You need to add an explicit locale requirement to the type. In my case this means that to format a cell to 000 with a UK locale then the custom type that I must define is [$-809]000

A list of magic numbers for other locales is here:
http://office.microsoft.com/en-gb/excel ... 34635.aspx

Ken

[Rudi]

Excellent...

South Africa locale format will be this: [$-1C09]000

[stuck]

What a team! The Lounge is greater than the sum of its parts

or was it the blind leading the blind?

Ken

[Rudi]

The latter....BUT a miracle happened and now we can see...

[rory]

Does it actually make any difference what locale is showing?

[HansV]

It's not serious, of course, but it's annoying to have the Special number formats displayed in a script most of us can't read even when you select a different cell:

S317.png

You have to select your own language each time...

[stuck]

Also, I have a suspicion that the odd formatting was causing some instability in my workbook. However, I can't prove anything and the rest of the workbook is quite complicated and involves some ropey VBA I wrote ages ago.

Ken

[Jan Karel Pieterse]

Odd, sounds like a bug to me.
It reproduces in 2003 too, so I expect this has been in Excel for a long time.