Number Formats and Locale Settings

[Alan2Po]

[Alan2Po]

And hope this error will be correced.

[JJJoseph]

[jrkrideau]

[John_A]

Hi folks.

The locale issue is quite contentious, and I don't want to offend anyone, but I have a few comments and questions. My focus is the EN ZA (English South Africa) locale.

Who sets the locale defaults in the XML file? Can the information be traced back to a standards organisation (the SABS, in my case), or is it supplied by a "knowledgeable volunteer", from his/her own experience?

My reason is this: the thousands separator listed in the XML file (and in the localedata_en.dll file, obviously) is "," while several hundred thousand documents published here use " " as a thousands separator. There may be industries where the "," is used, but similarly, there are industries here with their own standard way of presenting data. For example: the financial industry uses " " exclusively, including the big four auditors, publishing thousands of financial statements annually, thousands of accountants, and SARS, our "inland revenue department".

With such a large base of (frustrated) users, is it possible to amend the default to " ", rather than ",", or at least publish a new "English South Africa - Finance" locale for these users (A very high percentage of OOo users, probably more than 50%, I'd guess)?

What is the accepted way of dealing with this problem. Surely other countries have multiple industry standards, if not multiple technical standards.

Another option would be for users like myself to choose an existing locale which conforms to our industry standard. Unfortunately, I have found none (after examining dozens), as the locales which use " " as a thousands separator tend to use "," as a decimal separator, while we use ".". Is there any quick table available showing the various locales and their defaults?

Hopefully I can resolve this issue without having to hack the localedata*.dll files, or compile a custom build. (I'm a noob at that).

Thank you for your time,

John.

[r.johnson]

[darice]

Hi all,

Im very new to this and not as advanced as you all appear to be I need someone to explain really simply how I can switch automatic calculations off.
I want to write 11am it keeps coming up with 11:00:00.
I dont want it to automatically write 11:00am either because i understand how to get it to do that I just want it to simply say 11am.
I am so frustrated and sick of trying.
I would really appreciate some help.
Thanks Darice

[Villeroy]

Do not use spreadsheets at all.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Villeroy]

Short abstract
All Excelish spreadsheets of the past 2 decades behave more or less in the same way. If you can't handle this, then you must not use spreadsheets [I never get tired to recommend databases where you can enforce almost anything -- if your can].

0) Every cell has a number format locale. Number format locale "Default" falls back to the application wide locale in the language options. Most importantly, the locale context determines the decimal separator (comma or point) and if 12/1/1999 means 12th of January or December the 1st (US).
1) All your input is evaluated as some type of number within the locale context. The value is text if this evaluation fails.
2) With a leading = your input is evaluated as number. The result is some error value if the evaluation fails.
3a) A leading quote inhibits all evaluation. All input is literal text. Text "123" is not a number. It is a sequence of characters like "abc" is.
3b) Like any other formatting attribute (color, font, border,...) number format "@" (text) has absolutely no influence on existing data, BUT all new input will be treated as in 3a).
4) All your numbers will be shown in one particular number format, namely the number format you have applied. If you do not apply any number format (number format "General") then your input is shown in one particular number format too. The particular number format depends on the locale and the type of number you typed. There is one particular number format for integers and floating point numbers, one for percent input, one for date input, one for time input and for date+time input, there is one for your input of a scientific number and a one particular boolean number format showing TRUE and FALSE in the language of the cell's locale.
There is no way to modify these defaults.
One particular number format is the key which makes your documents portable across language boundaries and office versions.

It is completely pointless to complain about this or that number format. Number formatting is not important at all as long as the values are the right ones.
Many confused beginners expect some auto-formatting which formats your numbers exactly like you typed them. This is not possible, not even desireable. You could hardly tell if your input has been taken as number or text.

Convert numbers to text and text to numbers:
menu:Edit>Find&Replace...
[More Options]
[X]Regular expressions
[X]Current selection only
Search: .+ (dot and plus)
Replace: &
[Replace All]
This replaces every value with itself. If the cell is formatted as text, the input turns into text, any other number format turns the entry into a number.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Lino]

Yeah, forced date formatting is annoying. More than the Orange.

As much as I can see, the problem is in formatting order. It is:
1. try in any case to make input a number
2. if not possible, make it text or something

The solution would be to stop forcing number formatting before date format and make it text. Date formatting apply only if cell formatted as date.

Any idea how to make it?

[Villeroy]

[BRT1]

[bernard_ivo]

Dear all,
I have tried to find an answer to that question, but apparenlty I couldn't. So here is what I'm looking solution to.
I'm transforming a table from .PDF to .XLS and the numbers in the PDF are formated this way:
234,456 = two hundred thirty four thousand four hundred fifty six
when the .XLS is opened in OO the number is presented
234.456 - the comma was not interpeted as thousands separator but a decimal sign.

I have played with locale seetings but the only result is that it set either comma or a dot for decimal sign and the number is not recognised as the integer for "two hundred thirty four thousand four hundred fifty six". I would like to convert the number to 243,456.00 without having to rewrite every single number in the tables. Is that possible and how?

Cheers

[kevin.burton]

now i do understand how openoffice works thanks to all your help

[remo00]

I think some wrong set up exists. Try typing the following

With English(UK) locale
[comma(,)=thousand, dot(.)=decimal, slash(/)=date, column( : )=hour]
12(dot)3 is recognised as a number 12decimal3-OK-
12(comma)3 is a STRING -OK-
12(comma)345 is the number 12345 –OK-

With Greek locale
[dot(.)=thousand, comma(,)=decimal, slash(/)=date, column( : )=hour]
12(comma)3 is recognised as a number 12decimal3 -OK-
12(dot)3 is the date 12/03/2012 -WRONG should be a STRING-
12(dot)345 is the number 12345 –OK-

With French (France) locale
[space( )=thousand, comma(,)=decimal, slash(/)=date, column( : )=hour]
12(comma)3 is recognised as a number 12decimal3 -OK-
12(dot)3 is the date 12/03/2012 -WRONG should be a STRING-
12(dot)345 is the date 01/12/345 -WRONG should be a STRING-

So, when dot(.) isn't the decimal separator, all entries with numbers and dot are incorrectly interpreted as date even if the date separator are different (slash or hyphen)
In those cases number with dot should be interpreted as STRING.

Said that, I really don't know how to correct this.

Cheers