Can this be done??

W

WarrenC

I was hoping that Excel can take a series of letters, un-jumble them and
then compare the result with words in the dictionary and tell me which
ones are actual words. Can something like this be done?

I like doing those "Word Jumble" puzzles, but sometimes need help to
solve them

Warren
 
K

kassie

If each series contains one word, it should be quite easy to figure out. It
is true that as long as the first and last letters of a word are in place,
the rest can be jumbled, and you would still be able to read the word. For
example, if you raed a pragrpah whtiuot wrorniyg aobut teh slepinlgyou can
raed a book
 
C

CWillis

Warren,

I wrote a program that does what you wanted. (It took me a while. I'm new
at this and I figure I ought to do something around here along the lines of
real work every once and a while since they pay me.)

The program takes the letters from an input, creates all possibilities
(factorial of the number of letters) then spell checks those possibilities.
Anything with 7 letters or less works great. The problem is that, depending
on computer speed of course, 8 letters takes approx. 10 minutes, 9 letters
takes about 2 hours and 10 letters is just shy of a day. If this would be of
interest, let me know and I will post the code (which isn't short due to my
primative coding skills.)

-Chris
 
W

WarrenC

Chris,

Please post the code. I would be very interested in it. From what
remember with that Basic program I wrote years ago, 5 letters too
hours and 6 letters took almost overnight. Your's is much improved.

Warre
 
W

WarrenC

Chris,

Please post the code. I would be very interested in it. From what
remember with that Basic program I wrote years ago, 5 letters too
hours and 6 letters took almost overnight. Your's is much improved.

Warre
 
C

CWillis

Ok, here you go Warren:

From the code you will see that you need a button called
"CommandButton1" and the word must be entered in Sheet1 in
cell B9. Right now the code can only "handle" 10 letter words
and time wise can only handle 7 letter words. At the bottom
I have copied some calculations on predicted time for 1-15 letter
words based on my slow laptop calculation speeds. Enjoy! Let me
know if it works and if you do anything to make it better/faster.

-Chris
---------------------------------
In sheet 1:
Private Sub CommandButton1_Click()

runme 'runs "runme" module

End Sub
----------------------------------
In a module called "checkspell":

Sub spellcheck()

Set objWord = Excel.Application


If objWord.CheckSpelling(wordtotest) Then
Sheet2.Cells(counter, 1).Value = wordtotest
counter = counter + 1
Else
counter = counter
End If

End Sub
----------------------------------
In a module called "Letterx":
Public char1, char2, char3, char4, char5, char6, char7, char8, char9, char10
Public a, b, c, d, e, f, g, h, i, j
Public wordtotest
Sub letter1()
a = 1
counter = counter
numberstochar
wordtotest = char1
spellcheck
End Sub
Sub letter2()
For a = 1 To 2
For b = 1 To 2
If a = b Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2
spellcheck
End If
Next b
Next a
End Sub
Sub letter3()
For a = 1 To 3
For b = 1 To 3
For c = 1 To 3
If a = b Or a = c Or b = c Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3
spellcheck
End If
Next c
Next b
Next a
End Sub
Sub letter4()
For a = 1 To 4
For b = 1 To 4
For c = 1 To 4
For d = 1 To 4
If a = b Or a = c Or a = d Or _
b = c Or b = d Or _
c = d Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 & char4
spellcheck
End If
Next d
Next c
Next b
Next a
End Sub
Sub letter5()
For a = 1 To 5
For b = 1 To 5
For c = 1 To 5
For d = 1 To 5
For e = 1 To 5
If a = b Or a = c Or a = d Or a = e Or _
b = c Or b = d Or b = e Or _
c = d Or c = e Or _
d = e Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 & char4 &
char5
spellcheck
End If
Next e
Next d
Next c
Next b
Next a
End Sub

Sub letter6()
For a = 1 To 6
For b = 1 To 6
For c = 1 To 6
For d = 1 To 6
For e = 1 To 6
For f = 1 To 6
If a = b Or a = c Or a = d Or a = e Or a = f
Or _
b = c Or b = d Or b = e Or b = f Or _
c = d Or c = e Or c = f Or _
d = e Or d = f Or _
e = f Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 &
char4 & char5 & char6
spellcheck
End If
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
Sub letter7()
For a = 1 To 7
For b = 1 To 7
For c = 1 To 7
For d = 1 To 7
For e = 1 To 7
For f = 1 To 7
For g = 1 To 7
If a = b Or a = c Or a = d Or a = e Or a
= f Or a = g Or _
b = c Or b = d Or b = e Or b = f Or b = g
Or _
c = d Or c = e Or c = f Or c = g Or _
d = e Or d = f Or d = g Or _
e = f Or e = g Or _
f = g Then
counter = counter
Else:
numberstochar
End If
Next g
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
Sub letter8()
For a = 1 To 8
For b = 1 To 8
For c = 1 To 7
For d = 1 To 8
For e = 1 To 8
For f = 1 To 8
For g = 1 To 8
For h = 1 To 8
If a = b Or a = c Or a = d Or a = e Or a
= f Or a = g Or a = h Or _
b = c Or b = d Or b = e Or b = f Or b =
g Or b = h Or _
c = d Or c = e Or c = f Or c = g Or c =
h Or _
d = e Or d = f Or d = g Or d = h Or _
e = f Or e = g Or e = h Or _
f = g Or f = h Or _
g = h Then
counter = counter
Else:
numberstochar
End If
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
----------------------------------
In a module called "num2char":
Sub numberstochar()
If numchar = 1 Then
char1 = Mid(word, a, 1)
ElseIf numchar = 2 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
ElseIf numchar = 3 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
ElseIf numchar = 4 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
ElseIf numchar = 5 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
ElseIf numchar = 6 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
ElseIf numchar = 7 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
char7 = Mid(word, g, 1)
wordtotest = char1 & char2 & char3 & char4 & char5 & char6 & char7
spellcheck
ElseIf numchar = 8 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
char7 = Mid(word, g, 1)
char8 = Mid(word, h, 1)
wordtotest = char1 & char2 & char3 & char4 & char5 & char6 & char7 &
char8
spellcheck
End If

End Sub
----------------------------------
In a module called "Run":
Public numchar As Integer
Public word As String
Public counter
Sub runme()
Dim start, finish, elapsed
start = Timer
counter = 1
word = Sheet1.Range("b9").Value
numchar = Len(word)
If numchar = 1 Then
letter1
ElseIf numchar = 2 Then
letter2
ElseIf numchar = 3 Then
letter3
ElseIf numchar = 4 Then
letter4
ElseIf numchar = 5 Then
letter5
ElseIf numchar = 6 Then
letter6
ElseIf numchar = 7 Then
letter7
ElseIf numchar = 8 Then
letter8
ElseIf numchar > 8 Then
MsgBox ("Please choose a word with 8 characters or less.")
End If
finish = Timer
elapsed = finish - start
Sheet1.Range("D1").Value = elapsed
End Sub
------------------------------------
# letters iterations pred sec pred min pred hours pred days pred years
1 1 -0.009222578 -0.00015371 -2.56183E-06 -1.06743E-07 -2.92446E-10
2 2 0.012688987 0.000211483 3.52472E-06 1.46863E-07 4.02365E-10
3 6 0.100335247 0.001672254 2.78709E-05 1.16129E-06 3.18161E-09
4 24 0.494743416 0.008245724 0.000137429 5.7262E-06 1.56882E-08
5 120 2.598253648 0.043304227 0.000721737 3.00724E-05 8.23901E-08
6 720 15.7451926 0.262419877 0.004373665 0.000182236 4.99277E-07
7 5040 110.4031531 1.840052551 0.030667543 0.001277814 3.50086E-06
8 40320 883.4431636 14.72405273 0.245400879 0.010225037 2.80138E-05
9 362880 7951.237545 132.5206258 2.208677096 0.092028212 0.000252132
10 3628800 79512.65566 1325.210928 22.08684879 0.920285366 0.00252133
11 39916800 874639.5236 14577.32539 242.9554232 10.12314263 0.027734637
12 479001600 10495674.63 174927.9104 2915.465174 121.4777156 0.332815659
13 6227020800 136443770.5 2274062.842 37901.04736 1579.210307 4.32660358
14 87178291200 1910212788 31836879.79 530614.6632 22108.9443 60.57245014
15 1.30767E+12 28653191814 477553196.9 7959219.948 331634.1645 908.5867521
*I don't know if that will copy and paste back into excel or not....
 
W

WarrenC

CWillis,

The code worked great. I just tidied things up a bit for display.
This was exactly what I was looking for.

thanks
Warre
 
Top