S
StargateFanFromWork
This one is not for work, for once, but I'm hoping someone will like to help
with this fun thing. I'd like to create a means to make part of the process
of creating word puzzles called syllacrostics automated. Since it's
something I hope to do on at least a weekly basis, it would be easiest to do
this in Excel because I need all the boxes (the cells and rows) and
automating it will allow me to concentrate on the puzzle itself rather than
in the creation of the word puzzle.
Here's what's needed after pasting a text string into A1:
1. Remove the spaces between the words.
2. Take that text string and cut it in half into 2 sentences.
3. Re-arrange the 2 "sentences" so that the letters are no longer
horizontal but vertical and, therefore, become vertical sentences in two
columns.
Separate the 2 setences-turned-into-columns by one empty column (width
unimportant at this point, though about 20 pixels great).
To demonstrate, a text string is copied into A1. In this example, I'll use:
"Fill in the answers to the clues by selecting the correct syllables from
the list below"
Step 1: spacing between words removed:
"Fillintheanswerstothecluesbyselectingthecorrectsyllablesfromthelistbelow"
Step 2: string cut in half (quotation marks so it's easier to see from this
point on):
Fillintheanswerstothecluesbyselectin
gthecorrectsyllablesfromthelistbelow
Step 3: the two sentences converted into two columns (there can only be 2
columns, that's why text string cut
in _half_) with an extra column in between:
F g
i t
l h
l e
i c
n o
t r
h r
e e
a c
n t
s s
w y
e l
r l
s a
t b
o l
t e
h s
e f
c r
l o
u m
e t
s h
b e
y l
s i
e s
l t
e b
c e
t l
i o
n w
Can this be done in Excel? I would prefer it as I am more comfortable
working in Excel's vbe for anything later that needs adding, that in Word.
Also, the results would come out in table format, which is what I need.
[I do have code for putting each letter into its own cell at some point,
though don't know if it can be used here. It was kindly given to me a few
weeks back:
Sub a_SelectText_ConvertsTextToOneCellPerLetter()
'
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array _
(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array(11, 1), Array _
(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23,
1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1),
Array(30, 1), Array(31, 1), _
Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36,
1), Array(37, 1), Array( _
38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1),
Array(43, 1), Array(44, 1), _
Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49,
1), Array(50, 1), Array( _
51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1),
Array(56, 1), Array(57, 1), _
Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62,
1), Array(63, 1), Array( _
64, 1))
End Sub
]
Thanks and cheers! D
p.s., some syllacrostic examples:
http://www.pennypress.com/samplepuzzles/srwoo15.pdf
http://www.puzzlechoice.com/pc/Syllax.html
http://www.aviewofamerica.com/Puzzles/Syllacrostic/syllacrostic.htm
with this fun thing. I'd like to create a means to make part of the process
of creating word puzzles called syllacrostics automated. Since it's
something I hope to do on at least a weekly basis, it would be easiest to do
this in Excel because I need all the boxes (the cells and rows) and
automating it will allow me to concentrate on the puzzle itself rather than
in the creation of the word puzzle.
Here's what's needed after pasting a text string into A1:
1. Remove the spaces between the words.
2. Take that text string and cut it in half into 2 sentences.
3. Re-arrange the 2 "sentences" so that the letters are no longer
horizontal but vertical and, therefore, become vertical sentences in two
columns.
Separate the 2 setences-turned-into-columns by one empty column (width
unimportant at this point, though about 20 pixels great).
To demonstrate, a text string is copied into A1. In this example, I'll use:
"Fill in the answers to the clues by selecting the correct syllables from
the list below"
Step 1: spacing between words removed:
"Fillintheanswerstothecluesbyselectingthecorrectsyllablesfromthelistbelow"
Step 2: string cut in half (quotation marks so it's easier to see from this
point on):
Fillintheanswerstothecluesbyselectin
gthecorrectsyllablesfromthelistbelow
Step 3: the two sentences converted into two columns (there can only be 2
columns, that's why text string cut
in _half_) with an extra column in between:
F g
i t
l h
l e
i c
n o
t r
h r
e e
a c
n t
s s
w y
e l
r l
s a
t b
o l
t e
h s
e f
c r
l o
u m
e t
s h
b e
y l
s i
e s
l t
e b
c e
t l
i o
n w
Can this be done in Excel? I would prefer it as I am more comfortable
working in Excel's vbe for anything later that needs adding, that in Word.
Also, the results would come out in table format, which is what I need.
[I do have code for putting each letter into its own cell at some point,
though don't know if it can be used here. It was kindly given to me a few
weeks back:
Sub a_SelectText_ConvertsTextToOneCellPerLetter()
'
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array _
(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array(11, 1), Array _
(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23,
1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1),
Array(30, 1), Array(31, 1), _
Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36,
1), Array(37, 1), Array( _
38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1),
Array(43, 1), Array(44, 1), _
Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49,
1), Array(50, 1), Array( _
51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1),
Array(56, 1), Array(57, 1), _
Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62,
1), Array(63, 1), Array( _
64, 1))
End Sub
]
Thanks and cheers! D
p.s., some syllacrostic examples:
http://www.pennypress.com/samplepuzzles/srwoo15.pdf
http://www.puzzlechoice.com/pc/Syllax.html
http://www.aviewofamerica.com/Puzzles/Syllacrostic/syllacrostic.htm