Description: I've imported a text file to calc, changed the default decimal delimiter settings, and tried to sort the list. Problem is that the listing is done alphanumerical, not numerical (10, 100, 20 instead of 10, 20, 100). Tried to find out what was going on and did some calculations. 90 / 10 = 9; 100 / 10 = 10 But 9 > 10 = false, 90 > 100 = true Steps to Reproduce: 1. Open file 2. Check calculations in row N69 3. Actual Results: 90 > 100 = TRUE Expected Results: 90 > 100 = FALSE Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 145206 [details] Testfile for checking numbers Check field N69
In your file all numbers have a dot as decimal separator and set up Norvegian language for cells. But in this country should be comma as decimal separator. Replace all dots to commas and check your "problem" else one IMHO -> NOTABUG
Created attachment 145207 [details] Test file after replace all dots to commas
We need to have dot as decimal delimiter, there are other programs that require that. In Windows settings we have changed that setting on all PC's. I have manually set language to english for that file.
(In reply to Andreas Zieritz from comment #4) > We need to have dot as decimal delimiter, there are other programs that > require that. > > In Windows settings we have changed that setting on all PC's. > > I have manually set language to english for that file. Did you change "Tools/Options.../Language Settings/Languages" -> Locale Setting ?
Yes, correct. I changed that one to English / Great Britain, and checked Decimal Delititer to local setting(.). Btw., I have no idea what it means if this checkbox is unticked.
(In reply to Andreas Zieritz from comment #6) > Yes, correct. > I changed that one to English / Great Britain, and checked Decimal Delititer > to local setting(.). > Btw., I have no idea what it means if this checkbox is unticked. from help: Decimal separator key - Same as locale setting Specifies to use the decimal separator key that is set in your system when you press the respective key on the number pad. If this checkbox is activated, the character shown after "Same as locale setting" is inserted when you press the key on the number pad. If this checkbox is not activated, the character that your keyboard driver software provides is inserted.
read about locale settings for decimal separator and about problems with working in Calc when you import data from one locale to another
ok, i changed my settings to "English (UK)" and after a restart i opened your test file: numbers are displayed (e.g. B6) as text: ' 10.000000 notice: there is a leading and trailing space, xml representation is "string" too: content.xml: <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p> <text:s/>10.000000 </text:p> </table:table-cell> you can select each row and use Menu "Data/Text to Columns..." to convert text to numbers, or, if your source data file is a *.csv for example, you will see the "Text Import" dialog during opening. tip: select Menu "View/Value Highlighting", so numbers will appear blue.
Apparently there are 2 things going on here. 1.) Decimal delimiters 2.) Type of content Apparently the contents of the cells are strings. The only way to see that is checking View/Value Highlighting. But then my first question is, why can I do calculation with strings at all? As the calculations do show, I can divide the contents of the cell by 10, and do the same logical test to those results. And voila - the result suddenly is correct. In my opinion, a string '+' of 10 + 10 should be 1010, and not 20, as it actually does. So those operations do not behave like string operations, but numeric ones. But when it comes to > they do behave like string operations. The other thing is that you should have a look at "Data/Text to columns...", because I really would not understand what this function would do. But ok, if I do open this function, I can choose e.g. column B. Column type however gives me no choice to choose anything useful (standard, text, date). Maybe I am missing something here. Last thing: Wouldn't it be nice if LibreOffice used the Windows Decimal Delimiter setting for Windows builds?
> But then my first question is, why can I do calculation with strings at all? please have a look at: https://help.libreoffice.org/Calc/Converting_Text_to_Numbers > The other thing is that you should have a look at "Data/Text to columns...", > because I really would not understand what this function would do. with "Locale Setting" Englisch (UK) - select column B - open dialog Data/Text to columns... - press [OK] this function will convert text values to float.