On Thu, 29 Mar 2007 20:03:46 -0400, "StargateFanFromWork"
wrote:

[snip]

If I understand you correctly, what you want to do is retain only the
letters,
both capital and small, and get rid of everything else -- spaces,
punctuation,
numbers.

I would do that using a simple regular expression routine to replace
anything
that is not a letter.

Try this:

=============================================
Option Explicit

Sub Syll()
Dim c As Range
Dim str As String
Dim i As Long
Dim oRegex As Object

Set oRegex = CreateObject("VBScript.RegExp")

Set c = Selection

With oRegex
.ignorecase = True
.Global = True
.Pattern = "[^a-z]"
str = oRegex.Replace(c.Text, "")
End With

For i = 1 To Int(Len(str) / 2)
c.Offset(i, 0).Value = Mid(str, i, 1)
Next i

For i = i To Len(str)
c.Offset(i - Int(Len(str) / 2), 2).Value = Mid(str, i, 1)
Next i

End Sub
=========================================

<g Much, much better than mine, Ron! I've had time to think about this
since I posted and I've figured out that I will not be able to use anything
with nunbers in it anyway. But this here is perfect. I'll be able to start
create Syllacrostics now. Totally awesome. And the time saved is
incredible.

Thanks!


You're welcome. Glad to help. Thanks for the feedback.
--ron