Sort scripture references by chapter number

[FryGirl]

Is there any way to get these scripture references to sort correctly by chapter number?

I know it has to do with the number being a 1 comes before a 4, but not sure how to get this corrected. The left is sorted A-Z with the correct on the right.

Scripture References.xlsx
	A	B	C
1	Hdr2		Hdr2
2	Matthew 11:28-30		Matthew 4:19
3	Matthew 16:24-25		Matthew 5:16
4	Matthew 18:20		Matthew 6:14
5	Matthew 22:37		Matthew 6:33
6	Matthew 25:13		Matthew 7:21
7	Matthew 28:18-20		Matthew 7:7
8	Matthew 4:19		Matthew 9:13
9	Matthew 5:16		Matthew 11:28-30
10	Matthew 6:14		Matthew 16:24-25
11	Matthew 6:33		Matthew 18:20
12	Matthew 7:21		Matthew 22:37
13	Matthew 7:7		Matthew 25:13
14	Matthew 9:13		Matthew 28:18-20
Sheet2

 

[ides315]

You need to extract the chapter number into a helper column a way that Excel understands as a number, as opposed to as text.

In cell B2, enter the following formula:

=INT(TEXTAFTER(TEXTBEFORE(A2,":")," "))

Drag it down to the end of the list of verses, and sort on the new column.

If your actual full list isn't confined to verses from Matthew, and could also have verses from books which themselves have a space in them ("Song of Songs", "1 Corinthians", etc.), then you can use this formula instead:

=INT(TEXTBEFORE(INDEX(TEXTSPLIT(A2," "), COLUMNS(TEXTSPLIT(A2," "))), ":"))

 

[FryGirl]

Thank you, this works great, but if my list does go to other books outside of Matthew, how do I know sort? In other words, if I now have a number on column be, that will not sort with the book will it?

 

[SunnyAlv]

Try this @FryGirl

this formula will Sort by Name, by Chapter, by Verse

=LET(
array,B2:B14,
satu,NUMBERVALUE(TEXTAFTER(TEXTSPLIT(B2:B14,":")," ")),
dua,NUMBERVALUE(TEXTSPLIT(TEXTAFTER(B2:B14,":"),"-")),
tiga,LEFT(B2:B14,1),
SORTBY(array,tiga,1,satu,1,dua,1))

 

[ides315]

Thank you, this works great, but if my list does go to other books outside of Matthew, how do I know sort? In other words, if I now have a number on column be, that will not sort with the book will it?

Ah - you want to be able to sort on (1) Book, then (2) Chapter. And I'm guessing maybe (3) Verse after that? I think you might want to split out each of those pieces of information (Book, Chapter, Verse) in its own column, to give you full control over how the sort works.

In B1 name the column "Book", then in B2 and down:

=TEXTBEFORE($A2," ",-1)

In C1 name the column "Chapter", then in C2 and down:

=INT(TEXTBEFORE(TEXTAFTER($A2," ",-1), ":"))

And in D1 name the column "First Verse", then in D2 and down:

=INT(TEXTBEFORE(TEXTAFTER(TEXTAFTER($A2," ",-1), ":"),"-",,,1))

When you sort the whole table (Data > Sort), just make sure to choose the order like this:

 

[ides315]

On second thought, you could also simplify the formula in D2 to just

=INT(TEXTBEFORE(TEXTAFTER($A2, ":"),"-",,,1))

 

[Peter_SSs]

this formula will Sort by Name, by Chapter, by Verse

It will not sort by name unless you use the whole name rather than just the first letter of the name - see two incorrectly sorted values in column E below using your formula structure.
Column F is your formula modified to use the whole name which then does sort those values correctly.

@FryGirl
Column D is a shorter & slightly different approach. In it I have assumed none of the chapter or verse values contain more than 3 digits, though easy to add a couple more "0"s if they can be longer.

24 01 12.xlsm
	B	C	D	E	F
1	Hdr2
2	Matthew 11:28-30		Luke 5:16	Luke 5:16	Luke 5:16
3	Mark 16:24-25		Luke 18:19	Luke 18:19	Luke 18:19
4	Luke 18:20		Luke 18:20	Luke 18:20	Luke 18:20
5	Luke 18:19		Mark 16:24-25	Matthew 4:19	Mark 16:24-25
6	Matthew 25:13		Mark 28:18-20	Matthew 6:14	Mark 28:18-20
7	Mark 28:18-20		Matthew 4:19	Matthew 6:33	Matthew 4:19
8	Matthew 4:19		Matthew 6:14	Matthew 7:7	Matthew 6:14
9	Luke 5:16		Matthew 6:33	Matthew 7:21	Matthew 6:33
10	Matthew 6:14		Matthew 7:7	Matthew 9:13	Matthew 7:7
11	Matthew 6:33		Matthew 7:21	Matthew 11:28-30	Matthew 7:21
12	Matthew 7:21		Matthew 9:13	Mark 16:24-25	Matthew 9:13
13	Matthew 7:7		Matthew 11:28-30	Matthew 25:13	Matthew 11:28-30
14	Matthew 9:13		Matthew 25:13	Mark 28:18-20	Matthew 25:13
Sort

Cell Formulas
Range	Formula
D2:D14	D2	=LET(r,B2:B14,SORTBY(r,TEXTSPLIT(r," ")&TEXT(TEXTSPLIT(TEXTAFTER(r," "),":"),"000")&TEXT(TEXTSPLIT(TEXTAFTER(r,":"),"-"),"000")))
E2:E14	E2	=LET( array,B2:B14, satu,NUMBERVALUE(TEXTAFTER(TEXTSPLIT(B2:B14,":")," ")), dua,NUMBERVALUE(TEXTSPLIT(TEXTAFTER(B2:B14,":"),"-")), tiga,LEFT(B2:B14,1), SORTBY(array,tiga,1,satu,1,dua,1))
F2:F14	F2	=LET( array,B2:B14, satu,NUMBERVALUE(TEXTAFTER(TEXTSPLIT(B2:B14,":")," ")), dua,NUMBERVALUE(TEXTSPLIT(TEXTAFTER(B2:B14,":"),"-")), tiga,LEFT(B2:B14,FIND(" ",B2:B14)), SORTBY(array,tiga,1,satu,1,dua,1))
Dynamic array formulas.

 

[SunnyAlv]

It will not sort by name unless you use the whole name rather than just the first letter of the name - see two incorrectly sorted values in column E below using your formula structure.
Column F is your formula modified to use the whole name which then does sort those values correctly.

Thanks for the correction @Peter_SSs

 

[FryGirl]

Thank you all for the help. @Peter, this did the trick.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.