Jump to content

Please read the Forum Rules before posting.

Photo

Sorting Scripture references in Excel


  • Please log in to reply
13 replies to this topic

#1 sburke39

sburke39

    New to Bible Support

  • Members
  • Pip
  • 2 posts
Offline

Posted 03 March 2015 - 06:29 PM

Hello all.

I am wondering if an Excel macro or other tool exists to sort scripture reference (eg, Gen 2:3, Amos 1:1, etc) into their KJV book order (Gen, Ex, Lev, ...Rev).  I suppose I could come up with something on my own, but am kinda hoping someone has already invented the wheel.  I need to do the sorting in Excel.  Would also like to do it in MS Word.  Can sort now, and it comes out alphabetical, but that's not as user friendly as the Biblical ordering.

Thanks!

 

 



#2 Tj Higgins

Tj Higgins

    e-Sword Fanatic

  • Members
  • PipPipPipPipPip
  • 465 posts
Offline

Posted 03 March 2015 - 07:15 PM

Hello all.

I am wondering if an Excel macro or other tool exists to sort scripture reference (eg, Gen 2:3, Amos 1:1, etc) into their KJV book order (Gen, Ex, Lev, ...Rev).  I suppose I could come up with something on my own, but am kinda hoping someone has already invented the wheel.  I need to do the sorting in Excel.  Would also like to do it in MS Word.  Can sort now, and it comes out alphabetical, but that's not as user friendly as the Biblical ordering.

Thanks!

 

This might be what you are looking for with regards to Excel:

 

http://www.jpsoftwar...bible-in-excel/



#3 Toma

Toma

    e-Sword Addict

  • Veterans
  • PipPipPipPip
  • 51 posts
Offline

Posted 04 March 2015 - 07:21 PM

Hello TJ Higgins,

 

In response to your post...

 

This might be what you are looking for with regards to Excel:

 

http://www.jpsoftwar...bible-in-excel/

 

 

Do you know if that software actually sorts Bible verses?  That was the initial question, and I also have been at a loss for something to sort a list of Bible verses into proper KJV, or Christian numbering?

 

Thanks,

Thomas



#4 JPG

JPG

    Bug Hunter

  • Members (T)
  • PipPipPipPipPip
  • 772 posts
Offline

Posted 05 March 2015 - 02:32 AM

You can put a sequence of scriptures like Rom 5:8; 2Co_5:19-21;Luke 2:14;  Tit_3:4; 1Jo_4:9-10; 1Jo 4:19 into the verse list, and there is an option to sort there. 1-66.

 

Veres list feature is through the main menu - > Bible -> Verse list. Add a title for your list, and then click the green + icon, and paste your list of verse refs separated by semi colons...

 

Jon



#5 Fourbit

Fourbit

    New to Bible Support

  • Members
  • Pip
  • 5 posts
  • LocationBillings, Montana USA
Offline

Posted 05 March 2015 - 11:34 AM

Sburke, Thank you for posting a question that I have thought about off and on. I don't use excel (have never been a fan of MS) but, I did search for an answer for OpenOffice. I found this solution that you may be able to convert to excel. The two programs are very close in operation. But OO is free, has a great support group and can read/convert excel files.

 

Thanks again for reminding me of a question I had.

 

Paul



#6 Tj Higgins

Tj Higgins

    e-Sword Fanatic

  • Members
  • PipPipPipPipPip
  • 465 posts
Offline

Posted 05 March 2015 - 03:48 PM

Hello TJ Higgins,

 

In response to your post...

 

 

Do you know if that software actually sorts Bible verses?  That was the initial question, and I also have been at a loss for something to sort a list of Bible verses into proper KJV, or Christian numbering?

 

Thanks,

Thomas

 

I do not think it does, but here is the KJV formatted for excel

 

http://spreadsheetpa...bible_in_excel/



#7 Toma

Toma

    e-Sword Addict

  • Veterans
  • PipPipPipPip
  • 51 posts
Offline

Posted 08 March 2015 - 12:06 AM

TJ,

 

Thanks so much for your response.  Grace and peace,

 

Thomas



#8 Roy

Roy

    e-Sword Fanatic

  • Members (T)
  • PipPipPipPipPip
  • 258 posts
Offline

Posted 08 March 2015 - 12:23 PM

Sburke,

You might consider this.

e-Sword uses a database structure to store its data.
The bible database has three fields in particular, one each for Book, Chapter, and Verse.

The way it handles verses is this.

The Books are numbered 1-66
The chapters and verses are what they are.

So for example, if I wanted to access John 3:16 from the database file I would need to go to the record where Book=43, Chapter=3, and Verse=16.

You could do this by assigning a column for BCV (Book:Chapter:Verse) and put the data in the proper cell like this "43|03|16" or "43:03:16" or even "43.03.16".

Keep in mind that the data would be Character so you need to make sure you use a leading Zero. Example 03:16 and not 3:16.

If you don't you would get a wonky verse order 1,11,1213....18,19,2,20,21,22,....etc. (Because 1 comes before 2, so 1,11,12 would also come before 2 because you would be sorting characters, NOT numbers!!)

Hope this gives you some ideas.

Roy

 

Edit: Oops, I should have added, Then you would sort on this "Special" Column and your verses would be in proper order.

 

Roy


Edited by Roy, 08 March 2015 - 12:26 PM.


#9 Roy

Roy

    e-Sword Fanatic

  • Members (T)
  • PipPipPipPipPip
  • 258 posts
Offline

Posted 08 March 2015 - 12:39 PM

This link might help for Book Numbers.

 

I should also note that you "Can" use numbers in a single cell, you simply have to use the verse numbers relative to the whole bible.

there are 31,102 verses in a KJV so Genesis 1:1 = 1 and Revelations 22:21 = 31102.

 

Or...

 

You could use a 3 column system, one each for Book, Chapter, and Verse (just like is done in an e-Sword Bible file) and then you can use real numbers and not worry about leading zeros.

 

I would only worry about this last method if you were wanting to pull data out of your list's such as "How many verses does my list have from Chapter 3 of John?".

 

Roy

 



#10 Roy

Roy

    e-Sword Fanatic

  • Members (T)
  • PipPipPipPipPip
  • 258 posts
Offline

Posted 08 March 2015 - 09:26 PM

Here is a quick something I put together.

An Excel Spreed Sheet to help find Book|Chapter|Verse numbers & corresponding verse number in the whole Bible.

 

Nothing fancy.

Part came from a theWORD resource (The B|C|V Column).

Part came from the Web page I previously linked to. (The Number<>Book Chart)

And the verse number in the Bible column just came from an equation.

 

Maybe this will benefit someone.

 

Roy

 

(Corrected alignment issue on spreedsheet and new screen shot)

 

Attached Files


Edited by Roy, 08 March 2015 - 09:32 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users




Similar Topics



Latest Blogs