I have some spreadsheets that I have to sort according to the numbers in a particular column. That column contains three figures in each cell e.g. 1.53.1, which corresponds to Book 1, Chapter 53, Verse 1. The trouble is that Calc puts (for example) 1.11.xx after 1.1.xx rather than 1.2.xx, which is what I want to come next.
Now, I've tried selecting enable natural sort, and this produces better results for the first part of the sorted spreadsheet, but it goes out after 1.1.4, placing 1.12.7 next rather than 1.1.5 (which it places much later, after 1.10.5). I think that the problem is that there are three parts to the numbering.
Perhaps the solution is to create a user-defined category under Format Cells, in order to add leading zeros to the single digits (01.53.01 instead of 1.53.1), but I don't understand how to enter this. Any help would be appreciated.
[Solved] Sorting Book/Chapter/Verse Entries Numerically
[Solved] Sorting Book/Chapter/Verse Entries Numerically
Last edited by Hagar Delest on Thu Jun 21, 2018 7:56 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
LibreOffice (x64), Windows7
Re: Sorting Book/Chapter/Verse Entries Numerically
You might start by looking at
https://ask.libreoffice.org/en/question ... fice-calc/
https://ask.libreoffice.org/en/question ... fice-calc/
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Sorting Book/Chapter/Verse Entries Numerically
Thanks. I saw that, but it seems to use words (Gen, Exo, Lev, etc). I have only numbers in cells, so I don't know if that method can be made to work.
LibreOffice (x64), Windows7
Re: Sorting Book/Chapter/Verse Entries Numerically
To answer my own question...
I did it like this.
Split the three sections into separate columns (A,B,C, or whatever).
Data → Text to Columns → Separated by: Other: [.]
|1.53.1| becomes |1|53|1|
Data → Sort... → Sort Criteria → Sort Key 1 Column A *Ascending; Sort Key 2 Column B *Ascending; Sort Key 3 Column C *Ascending;
Done!
Sláinte
I did it like this.
Split the three sections into separate columns (A,B,C, or whatever).
Data → Text to Columns → Separated by: Other: [.]
|1.53.1| becomes |1|53|1|
Data → Sort... → Sort Criteria → Sort Key 1 Column A *Ascending; Sort Key 2 Column B *Ascending; Sort Key 3 Column C *Ascending;
Done!
Sláinte
LibreOffice (x64), Windows7