Comments on Natural Sort topic 112556

[Lupp]

CodeSnippet 112556 is locked.
Therefore here a kind of comment coming as a Calc document made with LibO V 25.2.1 Calc:

aoo112556_NaturalSortResearch.ods

(85.46 KiB) Downloaded 47 times

[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 "+").

[MrProgrammer]

See topic 112556.

Topic 112556 is Natural sort for OpenOffice Calc.

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.

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.

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.

[Lupp]

Thanks for your interest and your comments.
I enhanced my example document to exemplify my "research results" to more detail:

aoo112556_NaturalSortResearchEnhanced.ods

(109.89 KiB) Downloaded 25 times

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.

[Lupp]

See also: https://ask.libreoffice.org/t/help-text ... ort/119000