Created attachment 192474 [details] test case Version: 7.6.4.1 (X86_64) / LibreOffice Community Build ID: 60(Build:1) CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-GB Ubuntu package version: 4:7.6.4-0ubuntu0.23.10.1 Calc: threaded Jumbo Numbers in quotes, with a dollar sign appear to be detected as Numbers (when I check on the "Format Cells" but when I sort Descending, they aren't sorted by number, just alphabetically. What I would expect: Sort Descending, does sort descending by numerical value. Test case attached. Can someone reproduce in latest release?
Imported (using "Detect special numbers" ON) as: test1 $336.00 test2 $3.00 test3 $436.00 test3 $4,360.00 test3 $336.00 test3 $3,360.00 ts5t $995.00 test4 $9,951.00 afesf $995.00 Sort descending by column B: test4 $9,951.00 test3 $4,360.00 test3 $3,360.00 ts5t $995.00 afesf $995.00 test3 $436.00 test1 $336.00 test3 $336.00 test2 $3.00 That is using a recent 24.8 alpha. I admit that I don't understand the report. What is wrong (in whichever version)?
Perhaps this has been fixed recently? Could you share your full version information. Imported (using "Detect special numbers" ON) as: Sort descending by column B: test4 $9951.00 ts5t $995.00 afesf $995.00 test3 $4360.00 test3 $436.00 test3 $3360.00 test1 $336.00 test3 $336.00 test2 $3.00 ^ See the numbers are not sorted by nominal value. What I expected: test4 $9951.00 test3 $4360.00 test3 $3360.00 ts5t $995.00 afesf $995.00 test3 $436.00 test1 $336.00 test3 $336.00 test2 $3.00 ^ see the numbers are sorted by nominal value, the larger are at the top.
(In reply to Jonny Grant from comment #2) > ^ see the numbers are sorted by nominal value, the larger are at the top. That's exactly how I get it, sorted by value, the larger at the top, using either 7.6.3.2 or a recent 24.8 alpha on Windows. Maybe someone else using some recent Dev version on Linux might be able to reproduce. Maybe you have to review the options tab in the Sort dialogue. BTW, are you using the sort dialogue? Or instead, are you pressing on any specific icon that immediately sorts? Perhaps it would be better to post on <https://ask.libreoffice.org>?
(In reply to ady from comment #3) > (In reply to Jonny Grant from comment #2) > > > ^ see the numbers are sorted by nominal value, the larger are at the top. > > That's exactly how I get it, sorted by value, the larger at the top, using > either 7.6.3.2 or a recent 24.8 alpha on Windows. > > Maybe someone else using some recent Dev version on Linux might be able to > reproduce. > > Maybe you have to review the options tab in the Sort dialogue. > > BTW, are you using the sort dialogue? Or instead, are you pressing on any > specific icon that immediately sorts? There is a Sort button. If I do that and click options I can enable "natural sort" which it then does work. Or clicking menu "Sort" -> "Sort Descending" > > Perhaps it would be better to post on <https://ask.libreoffice.org>? I did think that, but I could reliably reproduce it in 7.6.4.1 On ubuntu. It's odd it works for you. Gnumeric it works without clicking any special options, it determines it is a currency and a number, so sorting works perfectly.
They are not importer as numbers, but text. Doesn't matter that the cell format is standard. I think the situation is solved with one of the next steps, to get column values as numbers: 1. Select as locale 'English US' in the Text import window. or 2. Click on the header of column with values and select US English for the column type, in the Text import window. Without the above steps, just with 'Enable natural sorting', the sorting is done correctly. It is there precisely for this kind of cases. Maybe Gnumeric has this option enable as default. There is not a bug.
(In reply to m_a_riosv from comment #5) > They are not importer as numbers, but text. Doesn't matter that the cell > format is standard. I want to be clear. Following the steps that I described (i.e. using "Detect special numbers"), the columns as Standard and locale as English USA (in the upper field of the import dialogue), the values are imported as numeric formatted as currency, not as text. The numeric values can be checked by Value Highlighting and by the ISTEXT() function. They are also aligned to the right by default, without having to set it manually. Having the locale as English USA helps in identifying the dot/period as decimal separator and the $ as currency, when it is used with "Detect special numbers". I have the option for "Natural sort" disabled for the sort dialogue.
Thank you for the replies. Note, this works without any clicking or changes in Gnumeric Note, it does not work in Google Sheets Does anyone have a copy of Excel to try with? When I consider as a Software Engineer, yes, I can try all these ways to do it myself on load. When I consider from the point of view as user, we just expect it works. Yes, indeed I can change to English(US) and it loads, although oddly it swaps the $ USD to be a £ GBP symbol! that's a bug -- but depends if anyone wants to fix it? I see it is already closed, so feels like no one agrees to match Gnumeric behaviour
(In reply to Jonny Grant from comment #7) > Yes, indeed I can change to English(US) and it loads, although oddly it > swaps the $ USD to be a £ GBP symbol! that's a bug -- but depends if anyone > wants to fix it? Your reports are not reproduced by anyone ATM. Please go to ask.libreoffice.org and try with other users to either replicate your problem, or search for workarounds, or both. As for this report being closed, if you can prove that there is really a bug as you described in comment 0 that can be replicated by others, please feel free to re-set this as unconfirmed. But, please, be sure that you have clear steps, clear actual results, clear expected results, and that others can reproduce the same problem.
(In reply to m_a_riosv from comment #5) > They are not importer as numbers, but text. Doesn't matter that the cell > format is standard. > > I think the situation is solved with one of the next steps, to get column > values as numbers: > > 1. Select as locale 'English US' in the Text import window. > or > 2. Click on the header of column with values and select US English for the > column type, in the Text import window. > > Without the above steps, just with 'Enable natural sorting', the sorting is > done correctly. It is there precisely for this kind of cases. Maybe Gnumeric > has this option enable as default. Perhaps it's an "enhancement"? to do what users expect, (match as Gnumeric already does), it's logical that strings containing numbers, would be sorted by their nominal value. I saw in "Format Cells" that those fields were detected as "Currency". So it is surprising again, that given a currency amount is a number.. it wouldn't sort by the nominal value. Is that another "enhancement" request? > > There is not a bug. Reminds me of people characterising an issue as "a feature, not a bug".
(In reply to Jonny Grant from comment #9) > sort by the nominal value. Is that another "enhancement" request? Let's recap. If we cannot replicate the problem, there is no way to improve the case. You see it sorted in one way, but I see it sorted as numbers (aka as you expected). Miguel was not able to repro the reported behavior either, and he closed the report as NAB. I mentioned the possibility of re-opening the report again. I already suggested to reach the help of other users at https://ask.libreoffice.org>. Have you? IDK what else you would expect at this point in this report. As a simple user as I am, if you present a bug report that I could replicate, I will contribute in any way I can.
Thank you for your message. Yes, the solution is to imported (using "Detect special numbers" ON), it's rather manual, not automatic as users would expect. That's fine, I'm a software engineer, I am used to changing settings. It's better just leave it on by default (perhaps behind an advanced) button as that is what users expect. There is another issue, loading as English(UK) with "Detect special numbers" ON, LibreOffice oddly changes the currency from USD to GBP. Around the world we often open CSV files, they may contain dollars, yuan, euros or pounds. We don't want LibreOffice to silently change the currency, that's a bug.