Function ParseStr(sStr1 As String)
Dim varr As Variant
Dim sStr As String
Dim sChr As String
Dim sStr2 As String
Dim bLast As Boolean
Dim i As Long
varr = Empty
Dim ub As Long
sStr = sStr1 & " "
ReDim varr(1 To 1)

If Len(sStr) = 0 Then
varr(1) = ""
ParseStr = varr
Exit Function
End If
sStr2 = Mid(sStr, 1, 1)
ub = 1
For i = 2 To Len(sStr)
sChr = Mid(sStr, i, 1)
If LCase(sChr) = UCase(sChr) Then
bLast = True
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
Else
If bLast Then
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
bLast = False
Else
sStr2 = sStr2 & sChr
bLast = False
End If
End If
Next
ParseStr = varr
End Function

you can use this to test it. It displays words with - <- on each side
so you can see exaclty what is stored in the array. You wanted every digit,
punctuation mark and space treated as a word, so that is what it does. If
a character is a letter, then Ucase(letter) < lcase(letter) so treat it as
a word, else collect the letters as a string to form a word.

Sub TestParse()
Dim sStr As String, sStr1 As String
Dim v, i As Long
sStr = "See Spot123, 456 Sit."
v = ParseStr(sStr)
For i = LBound(v) To UBound(v)
sStr1 = sStr1 & "-" & v(i) & "< -" & vbNewLine
Next
MsgBox sStr1
End Sub

--
Regards,
Tom Ogilvy


"jasonsweeney " wrote in
message ...
Tom,

Thanks for your help. I am still playing around with the code. Can you
point me in the right direction in parsing out the string for
particular punctuation? Just a few lines of conde for one punctuation
mark shoudl get me on the right road.

Again, I need to parse the text so that individual punctuation marks
are counted as "words', or, to be more specific, I need the punctuation
to parsed out so they occupy their own cell when the text is transposed
into excel cells.

Example 1: [See Spot Sit.] = 6 words, and when transposed needs to look
like (lets assume cells A1:A6):

A1 See
A2
A3 Spot
A4
A5 Sit
A6 .

Example 2: [I said to him "go yonder."]: 14 words

A1 I
A2
A3 said
A4
A5 to
A6
A7 him
A8
A9 "
A10 go
A11
A12 yonder
A13 .
A14 "


---
Message posted from http://www.ExcelForum.com/