[Solved] Sort text1 through text19 correctly

[procadman]

I'm sure this has been covered but I could not find any record of it.

I often have records that have a column containing 1 thru 19.

Using this column as the first section of a sort returns 1, 10, 11, 12, etc.

How do I force it to read correctly? e.g.: 1, 2, 3, ...10, 11, 12...

Feel free to direct me to a post that already covers this.

[robleyd]

Your "numbers" are text. Some related topics here:
viewtopic.php?t=45912
viewtopic.php?t=90617
viewtopic.php?t=83287

[Lupp]

LibreOffice has a sorting option "Enable natural sort" which would produce the results you (OQer) expected in the given case.
However, the variants of texts containing numerical parts are many, and the feature is of limited vaue.

[procadman]

re robleyd suggestions
I had already seen the 1st reference but I don't think it applies here.

Here is a sample of the ~350 records output:

exec3b1
exec3b10
exec3b11
exec3b12
exec3b13
exec3b2
exec3b3
exec3b4
exec3b5
exec3b6
exec3b7
exec3b8
exec3b9
facility1
facility10
facility11
facility12
facility13
facility14
facility15
facility16
facility17
facility2
facility3
facility4
facility5
facility6
facility7
facility8
facility9

Obviously the wanted result would be:

exec3b1
exec3b2
exec3b3
exec3b4
exec3b5
exec3b6
exec3b7
exec3b8
exec3b9
exec3b10
exec3b11
exec3b12
exec3b13
facility1
facility2
facility3
facility4
facility5
facility6
facility7
facility8
facility9
facility10
facility11
facility12
facility13
facility14
facility15
facility16
facility17

[Hagar Delest]

Here is a sample of the ~350 records output:
[...]

That would have been great to have it in your first post so that it shows precisely what your data look like.
But the root cause is the same as said in the other topics: your strings contain text, they are therefore considered as text and sorted based on their ASCII value, that doesn't separate the numbers from the text part.
They are workarounds, using a helper column to separate both parts.

LibreOffice has a sorting option "Enable natural sort" which would produce the results you (OQer) expected in the given case.
However, the variants of texts containing numerical parts are many, and the feature is of limited vaue.

Works pretty fine in this case:

Natural_sort.png (14.08 KiB) Viewed 4601 times

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the ✎ button if your issue has been fixed.

[Lupp]

LibO V25.2 has significantly changed the layout of option dialogs. Instead of tabs, there is now a kind of panel for the first level of selection.


Verry sorry! This is not the case for the sorting dialog.

[Lupp]

As I already hinted alphanumeric data containing numeric parts can occur in many different ways. A feature (in LibO) working as expected in a specific case may well fail in similar but different cases - or after a slight extension of what you already had.
Using such data should therefore always be based on a well considered and very strict format.
Example:
2025/facilty_/004
2021/exec3b/999
sort well even without "natural sort".
Their synatctial components are each of a fix length, and mandatory delimiters help to disambiguate cases which may possibly occur in the future. If (e.g) longer character sequences must be allowed for the middle part, a conversion of old data to the new format will also be simple.
Generally an extra column for each part of the compound information is preferable. It allows (e.g) to make the second part the most significant for another sort. Where a compound is wanted in addition it can be created by a formula in a very simple way. Columns are very cheap! And they can easily be hidden (as a group where needed) if the user feels they spoil the view.