King James Bible Numerics Calculations

[floridabruce1960]

Hi,

I created a spreadsheet that takes a string of text (specifically Bible verses from the King James Bible) and then will allow you to create another string of text from the original string of text pulling every 3rd letter, or 4th letter or 7th letter, etc., etc. Bible numerics if you will... In other words when I type in the first few words of Genesis 1:1 "inthebeginninggodcreated" and I have it set for every 8th letter, it renders the letters "GOD". Another example is Luke 2:21... When I type in the first 4 words of this verse "andwheneightdays" speaking about when JESUS was circumcised, and have it set for every 3rd letter, it renders the letters "DEITY". So, that's what my program does now... I'd like to be able to find a way to make it accept a larger chunk of text more easily... How can I do this?

Thanks!

Bruce Roberts

 

[Macropod]

Since you haven't shown us what you're now using, it's a bit hard to expect anyone to advise on how it might be improved.

 

[Macropod]

Cross-posted at: Is There An Easier Way and Ability To Enter Larger Texts / Bible Verses?
Please read Mr Excel's policy on Cross-Posting in rule 13: Message Board Rules

 

[floridabruce1960]

I have not figured out how to share a mini sheet. I tried following the instructions. too confusing. I cannot post a screenshot. My hands are kind of tied at the moment.

 

[Macropod]

You could employ a User-Defined Function (UDF) to parse text inserted into any designated range in the workbook.

For example, you could insert the following code into a standard VBA code module:

Public Function CreateString(xlRng As Range, Start As Long, Interval As Long) As String
  Dim xlCell As Range, i As Long, StrIn As String, StrOut As String
  Application.Volatile
  For Each xlCell In xlRng.Cells
    StrIn = StrIn & xlCell.Text
  Next
  'Strip out unwanted characters, including numbers, spaces & punctuation.
  For i = 1 To 255
    Select Case i
      Case 1 To 64, 91 To 96, 123 To 255: StrIn = Replace(StrIn, Chr(i), "")
    End Select
  Next
  For i = Start To Len(StrIn) Step Interval
    StrOut = StrOut & Mid(StrIn, i, 1)
  Next
  CreateString = LCase(StrOut)
End Function

There is no need with the above code to strip out any spaces, numbers, punctuation, etc. - the code does all that for you.

Then, in your workbook, you'd use a formula like:

=CreateString(A1:A5,5,8)

where
• 'A1:A5' defines the range containing the text;
• 5 defines the character to start parsing at; and
• 8 defines the character interval.

For example, with:

1:1 In the beginning God created the heaven and the earth.

in A1 and:

=CreateString(A1,8,8)

in your chosen output cell, the formula would return:

godee

Note: Your workbook will need to be saved in the xls or xlsm format to use the above UDF.