Page 1 of 1
Comments on Natural Sort topic 112556
Posted: Mon Mar 03, 2025 3:36 pm
by Lupp
CodeSnippet 112556 is locked.
Therefore here a kind of comment coming as a Calc document made with LibO V 25.2.1 Calc:
Re: Natural Sort. See topic 112556.
Posted: Mon Mar 03, 2025 4:21 pm
by Lupp
Spent a bit more time on this.
Preliminary result:
The items seem to be split in "numeric parts" and "non-numeric parts" in the order of occurrence. Sorting is then done in a way as if these parts are written into consecutive columns, and sorted by them with descending relevance from left to right.
If we assume that there is a maximum of 10 parts, in LibO Calc the formula
Code: Select all
{=IFNA(REGEX($B1; "\D+|\d+"; ; COLUMN(OFFSET(INDIRECT("$A$1"); 0; 0; 1; 10))); "")}
would create the column contents.
How signs are treated looks like a special mess to me. (Withdrawn remark.)
Decimal delimiters
and signs seem to be treated as text ("," LT "." // "-" LT "+").
Re: Natural Sort. See topic 112556.
Posted: Mon Mar 03, 2025 6:19 pm
by MrProgrammer
Lupp wrote: ↑Mon Mar 03, 2025 3:36 pm
See topic 112556.
Topic 112556 is
Natural sort for OpenOffice Calc.
Lupp wrote: ↑Mon Mar 03, 2025 3:36 pm
Who dares to describe the behavior exactly?
(in the attachment)
After reading the vague explanation of the behavior in the LO help, I decided:
Not me. But I remembered the types of topics in the Calc forum which ask about natural sorting, even if they don't use that name, and realized that a simple 30-line StarBasic function which supports text followed by an unsigned integer would solve many of them, for example,
How do I sort numerically with alpha prefix? and
[Solved] Sort text1 through text19 correctly.
Lupp wrote: ↑Mon Mar 03, 2025 4:21 pm
The items seem to be split in "numeric parts" and "non-numeric parts" in the order of occurrence. Sorting is then done in a way as if these parts are written into consecutive columns, and sorted by them with descending relevance from left to right.
Wow! That's very interesting. Nice research. And a more complex StarBasic function could implement that behavior. But I choose let others create such a function. I locked the Code Snippets topic so discussion like this would stay separate from that simple idea and function, since I wasn't going to make any proposed changes to have it duplicate the undocumented behavior in LibreOffice. I think many people have situations which NSKEY supports, are looking for quick OpenOffice solutions, and don't want to wade through dialog that isn't relevant to those situations.
Lupp wrote: ↑Mon Mar 03, 2025 4:21 pm
How signs are treated looks like a special mess to me. Decimal delimiters seem to be treated as text ("," LT ".").
Ha Ha. Maybe the reason the behavior isn't documented is that the explanation would be so complicated that:
• it would need to be quite long,
• it would be difficult to understand.
Re: Natural Sort. See topic 112556.
Posted: Mon Mar 03, 2025 7:40 pm
by Lupp
Thanks for your interest and your comments.
I enhanced my example document to exemplify my "research results" to more detail:
Of course, the REGEX() part will only work in LibreOffice Calc V 6.2 or higher.
There are also more then 3 levels of sorting. Not supported by AOO.
Re: Natural Sort. See topic 112556.
Posted: Mon Mar 03, 2025 8:37 pm
by Lupp