Hi
In LibreOffice Calc I would like to apply natural sort (alpanumerical sort) on cells, which contain word delimeters.
The Enable natural sort option is working only, when cell content does not delimited with space, underscore, etc.
See example:
No delimeter before the numeric suffix:
foo1
foo2
foo3
foo10
foo11
Underscore before the numeric suffix:
foo_1
foo_10
foo_11
foo_2
foo_3
Space before the numeric suffix:
foo 1
foo 10
foo 11
foo 2
foo 3I cannot find the way to remove the de-limiting characters.
The unicode char U+00A0 (non-breaking space) is directly available inside writer and acts exactly as described. It is not available from the menu in Calc in the same way, but it is available via Menu:- Insert Special Character....
I copied your 1st list into Calc & made sure that Natural Sort was working as expected (it was).
I copied the 3rd list into the next column in Calc & confirmed that Natural Sort was not working as expected (it was not).
Finally, I copied U+00A0 nbs into the clipboard & replaced all of the U+0020 spaces in Column B with U+00A0 spaces & tried again. It made no difference to the sort order.
From the above I conclude that Calc is inflexible to both Unicode special characters & character de-limiters.
As I understand it, Char will sort both within a cell & also a range of cells. In the former case it clearly needs a list of characters that can act as de-limiters in order to be able to split a string of characters into “words”. However, that should not apply when a natural sort is performed upon a cell range. But I suspect that it is. Anyway, whatever the reason, it is not performing as expected.
Bugs should go to Bugzilla, either directly or via the Bug Submission Assistant.
I repeated your example, with the same results. I then inserted a leading 0 before the single digit integers in each cell. It then sorts the way I think you want it to. SortingSample.ods
I don’t fully understand the differences in the behavior, but I think it’s the difference between alpha and numeric, if that makes sense. So the work around, if possible for you, is to use a fixed number of digits for the numerical appendage.
One thing that is further confusing is that the 0 in cell E2 is a plain 0, all the others display with a dot in the middle. Also, column F automatically created initial capital letters.
HTH
Edit: I just discovered that the .ods file I tried to attach from my hard drive is not available by clicking the hyperlink. I apparently don’t understand how the attachment icon works. If someone can tell me, I’ll actually attach it.
Hi
You’re right, the natural order does not work with the space or underscore. However it works with - (minus).
May be it is possible to replace with Edit Find & Replace or add a “sort” column with a function such as:
=SUBSTITUTE(A1;" ";"-")
Regards