An AOL User: What five letter word appears in the KJV only four times? It was made before Adam and named by Adam. Had no legs, arms or soul. Was given a soul and it was taken back.
Using J-Walk’s Bible in Excel, I listed all of the five letter words that appear only four times. Here’s the code:
Sub FindWord()
Dim sh As Worksheet
Dim rCell As Range
Dim colWords As Collection
Dim vaWords As Variant
Dim i As Long
Dim sText As String
Dim vaRemove As Variant
Const lLEN As Long = 5
Const lFREQ As Long = 4
Set colWords = New Collection
vaRemove = Array(“,”, “.”, “:”, “;”, “!”, “?”, “(“, “)”)
For Each sh In ThisWorkbook.Worksheets
For Each rCell In Intersect(sh.Columns(2), sh.UsedRange).Cells
sText = rCell.Text
For i = LBound(vaRemove) To UBound(vaRemove)
sText = Replace(sText, vaRemove(i), “”)
Next i
vaWords = Split(sText, ” “)
For i = LBound(vaWords) To UBound(vaWords)
If Len(vaWords(i)) = lLEN Then
On Error Resume Next
colWords.Add vaWords(i), CStr(vaWords(i))
On Error GoTo 0
End If
Next i
Next rCell
Next sh
For i = 1 To colWords.Count
Sheet1.Range(“G2”).Value = colWords(i)
If Application.WorksheetFunction.Sum(Sheet1.Range(“G5:G70”)) = lFREQ Then
Debug.Print colWords(i)
End If
Next i
End Sub
Dim sh As Worksheet
Dim rCell As Range
Dim colWords As Collection
Dim vaWords As Variant
Dim i As Long
Dim sText As String
Dim vaRemove As Variant
Const lLEN As Long = 5
Const lFREQ As Long = 4
Set colWords = New Collection
vaRemove = Array(“,”, “.”, “:”, “;”, “!”, “?”, “(“, “)”)
For Each sh In ThisWorkbook.Worksheets
For Each rCell In Intersect(sh.Columns(2), sh.UsedRange).Cells
sText = rCell.Text
For i = LBound(vaRemove) To UBound(vaRemove)
sText = Replace(sText, vaRemove(i), “”)
Next i
vaWords = Split(sText, ” “)
For i = LBound(vaWords) To UBound(vaWords)
If Len(vaWords(i)) = lLEN Then
On Error Resume Next
colWords.Add vaWords(i), CStr(vaWords(i))
On Error GoTo 0
End If
Next i
Next rCell
Next sh
For i = 1 To colWords.Count
Sheet1.Range(“G2”).Value = colWords(i)
If Application.WorksheetFunction.Sum(Sheet1.Range(“G5:G70”)) = lFREQ Then
Debug.Print colWords(i)
End If
Next i
End Sub
It uses the “Stats” page in the workbook to compute the number of times each word appears. This is not a good way to do it, because each word causes an expensive recalc. But I didn’t want to spend time writing my own algorithm. I started the macro and came back 20 minutes later.
Dick, there is actually quite a simple way to do the word count. You first set up an array alongside the collection, to hold the count, and another array, to hold the matching list of words, like so
Dim wordCount(100000) As Long
Dim Words(100000) As String
Then where you add to the collection, you do it like this…
colWords.Add colWords.Count + 1, CStr(vaWords(i))
..this will give you the correct location in your arrays to store each word and increment the count
This line stores the word if it hasn’t been seen before (immediately after the line above)
If Err.Number = 0 Then Words(colWords.Count) = vaWords(i)
and after you close the error trap, you increment the word count
On Error GoTo 0 ‘from your code
j = colWords(CStr(vaWords(i))) ‘look up the array location in the collection
wordCount(j) = wordCount(j) + 1
Then it’s simple to run through the arrays when you’ve finished, looking for those with a count of 4
This technique of putting an incrementing counter in a collection is very useful where you want to store data for each of the unique items in a collection, and I’ve used it many times.
use a dictionary iso a collection, and keep count while you’re finding the words. also use an array of string iso a variant for a split’s result. set the dic’s .CompareMode = TextCompare to avoid missing words due to capitalization.
If Len(asWords(w)) = 5 Then
If dWord.Exists(asWords(w)) Then
dWord(asWords(w)) = dWord(asWords(w)) + 1
Else
dWord.Add asWords(w), 1&
End If
End If
Next
Top 6:
shall9837
which4413
their3932
there2298
house2024
shalt1617
Shall and shaltin the top6… maybe that’s why I’m no bible fan(atic). Unique words used to wrote the bible: 13018..
cheerz from amsterdam
Q: “What five letter word appears in the KJV only four times?”
A: abase, Abiah, adder, Ahiah, Alpha, alter, amiss, Annas, apply, Arpad, Ashan, Attai, Azgad, baked, barns, bells, BIBLE, blame, blown, brown, chide, climb, cloak, crush, cured, darts, deeps, Derbe, Deuel, dowry, drops, Dumah, edges, Epher, Etham, extol, fears, filth, fitly, flute, fresh, Gaash, Golan, gross, heels, hoary, Hosea, Italy, Jabez, Jebus, knock, laded, lothe, lover, Mesha, Micha, navel, nests, Oboth, Omega, outer, owest, pains, Pallu, piped, print, privy, quake, ranks, ruddy, Salem, Salma, seize, sever, shaft, shear, shorn, sores, stank, stays, steel, stoop, stout, stump, swell, swept, taxed, Timna, title, Uriel, Uzzah, viper, whips, wiped, woven, wrung, yokes, Zohar
Using a regular expressions object with a dictionary object (cheers, Dave):
Finished in 4.7033593749984 seconds
Dim Tmr As Double ‘runtime timer
Tmr = Timer
‘found copy at http://patriot.net/users/bmcgin/kjv12.txt
Dim vFF As Long, EntireBible As String
vFF = FreeFile
Open “C:kjv12.txt” For Binary As #vFF
EntireBible = Space$(LOF(vFF))
Get #vFF, , EntireBible
Close #vFF
‘make regex collection of 5 letter words, put uniques into dictionary object
Dim RegEx As Object, RegC As Object, RegM As Object, Dict As Dictionary
Set Dict = CreateObject(“scripting.dictionary”)
Dict.CompareMode = 1 ‘1=TextCompare
Set RegEx = CreateObject(“vbscript.regexp”)
With RegEx
.Global = True
.MultiLine = True
.Pattern = “[A-Za-z]{5}” ‘5 letter word
End With
If RegEx.Test(EntireBible) Then
Set RegC = RegEx.Execute(EntireBible)
For Each RegM In RegC
If Dict.Exists(RegM.Value) Then
Dict(RegM.Value) = Dict(RegM.Value) + 1
Else
Dict.Add RegM.Value, 1
End If
Next
End If
Set RegEx = Nothing
Set RegC = Nothing
Set RegM = Nothing
‘Put 5 letter words and their associated count onto new one-sheet workbook
Dim AWord As Variant, TheWords() As Variant
Application.ScreenUpdating = False
With Workbooks.Add(-4167).Sheets(1)
.Name = “5 letter words”
.Range(“A1:B1”).Value = Array(“5 Letter Word”, “Word Count”)
TheWords = Dict.Keys
For Each AWord In TheWords
If Dict(AWord) = 4 Then ‘”What five letter word appears in the KJV only four times?”
.Range(“A1”).Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = _
Array(AWord, Dict(AWord))
End If
Next
End With
Application.ScreenUpdating = True
Set Dict = Nothing
Debug.Print “Finished in “ & Timer – Tmr & ” seconds”
End Sub
Shoot.. one slight change for late-binding:
Dim RegEx As Object, RegC As Object, RegM As Object, Dict As Object
I am surprised that you didn’t return some thing like “Paste”, “Chart”, “Table” and “Query”. This is my preferred Good Book by King John W.
(ok I am sure they appear far more in your book)
Don’t know if you are interestedin finding the word or an answer to a riddle.
The whale had no soul but was given a soul in the form of Janah which it swallowed. Then Joanh was taken from the whale.
The Bible does not mention a whale in Jonah’s story (or at all). Just says “a great fish”… neither does it say it was an Apple that Adam and Eve ate at the Garden. Very interesting nontheless…
Hi
Very interesting information! Thanks!
Bye
Quoted from Visitor: 27 June, 2007 12:39 pm
“The Bible does not mention a whale in Jonah’s story (or at all). Just says “a great fish”… neither does it say it was an Apple that Adam and Eve ate at the Garden. Very interesting nontheless…”
Actually the Bible does mention the word whale in the King James Version. But in the story of Jonah, it is called a big fish. The word whale is mentioned in Gen. 1:21, Job 7:12, Eze. 32:2 and Matt. 12:40. And Matthew is the one that mentions the story of Jonah. “For as Jonas was three days and three nights in the whale’s belly…” So if you are using the KJV of the Bible, then the word whale is mentioned and is very much the answer to the riddle that apparently started this whole thread.
Sarah-in Ezekiel it doesnt say “whale” it says “monster” and the kjv doesnt say anything about its soul being taken away or given or anything of the sort
Daniel-You are correct. The New KJV uses “monster” but the old KJV used “whale”. Sarah points out that the soul was that of Jonah whom the fish swallowed and then spit out. Thank you all for participating. Being a computer programmer myself, I was challenged to keep an open mind. The Genesis passage contained the five letter word hidden within the six letter word “whales”.
I am looking for an answer to a riddle. there is a lot more to the riddle than a five letter word mentioned 4 times in the king James versions. The word whale would fit migth fit.
thanks
Idon’t know ya’ll whale to me doesn’t fit the riddle. Well the one (riddle) I have any way.