Custom Number Format (97+/All)

Topic

New Reply

In Australia there is a number formatting standard that requires thousands to be separated by a space instead of the ” , ” (comma). I had tried customising a number format that includes commas as the separators but never achieved what was necessary. Then a light bulb went on and I decided that the Windows Settings needed to be changed (Control Panel, Regional Settings, navigate to ‘Digit Grouping Symbol’ and replace the comma with a space). (See attached)

Then, back in MS Excel in Format Cells, choose to use a number format that has the option ‘Use 1000 Separator ( )’. Note the space in the brackets.)

This has been great for most numbers but has a limitation. It will display correctly for two or three decimal places but does not display beyond that (e.g. 1 234 567.00 but 1 234 567.123456).

Any suggestions?

Reply | Quote

Replies

Separators only work on the integers, not the fractions; this is so with commas or any separator; they don’t show on the right side of the decimal place.

Try the following work-around where the spaces are ALT-NUMPAD entered as Alt-255:

=TEXT(cellref_or_calculation,”###

Reply | Quote

Thank you John for your quick reply.

I do need to use these numbers as numbers though. Therefore for presentation your response is OK but I can’t perform calculations on them. Or is there another way?

Leigh

Reply | Quote

BTW, who uses this unusual standard in Oz?

Reply | Quote

Thanks for the suggestions. I will try it out later today.

Re: Who uses the format? A government department that produces certificates prefers the ‘correct’ legal presentation. FYI, an Australian Standard was introduced in 1975 (when we changed to SI and the metric system) but like most of us we accept the general concensus that the commas are OK (especially as Microsoft has not been alerted to the requirement that we should adopt the standard in all produced material. Can’t blame MS when we as a group are tardy in adopting the change.)

Thanks again.

Reply | Quote

FYI: In Belgium, we use the comma as decimal separator and (often) the space as thousand separator. Just to make it more complex, especially if you want to write some international VBA stuff; you always have to take the Application.International(…) command as part of your programme.

Reply | Quote

Not easily, as =VALUE() will not directly recognise the format. You can use
=VALUE(SUBSTITUTE(cell_ref,”

Reply | Quote