[Solved] Sorting Book/Chapter/Verse Entries Numerically

Discuss the spreadsheet application
Post Reply
O'Brien
Posts: 3
Joined: Wed Jun 20, 2018 1:42 pm

[Solved] Sorting Book/Chapter/Verse Entries Numerically

Post by O'Brien »

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.
Last edited by Hagar Delest on Thu Jun 21, 2018 7:56 am, edited 1 time in total.
Reason: tagged solved
LibreOffice (x64), Windows7
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Sorting Book/Chapter/Verse Entries Numerically

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
O'Brien
Posts: 3
Joined: Wed Jun 20, 2018 1:42 pm

Re: Sorting Book/Chapter/Verse Entries Numerically

Post by O'Brien »

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
O'Brien
Posts: 3
Joined: Wed Jun 20, 2018 1:42 pm

Re: Sorting Book/Chapter/Verse Entries Numerically

Post by O'Brien »

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
LibreOffice (x64), Windows7
Post Reply