This will work if you're using xl2k or higher. It relies on VBA's Split command
that was added in xl2k.

Option Explicit
Function Get_Word(text_string As String, nth_word) As String

Dim mySplit As Variant
Dim TotalWords As Long
Dim myWord As String

myWord = ""

'remove any leading/trailing/multiple embedded spaces
text_string = Application.Trim(text_string)

If text_string = "" Then
'do nothing
Else
mySplit = Split(text_string, " ")
TotalWords = UBound(mySplit) - LBound(mySplit) + 1
'mySplit is 0 to (words - 1)
If (nth_word - 1) > UBound(mySplit) Then
'do nothing
Else
myWord = mySplit(nth_word - 1)
End If
End If

Get_Word = myWord
End Function
Sub testme()
MsgBox Get_Word("this is a test", 2)
End Sub




betty77 wrote:
>
> I inserted this code as a module, and then I tried to use it in a Macro,
> but it did not work. Can anyone help me to check this?
> ----------------------------------------------------------
> Option Compare Text
> Function Get_Word(text_string As String, nth_word) As String
> Dim lWordCount As Long
>
> With Application.WorksheetFunction
> lWordCount = Len(text_string) - Len(.Substitute(text_string, "
> ", "")) + 1
>
> If IsNumeric(nth_word) Then
> nth_word = nth_word - 1
> Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^",
> nth_word), 1, 256), _
> Find("^", .Substitute(text_string, " ", "^",
> nth_word)), 256), 2, _
> Find(" ", Mid(Mid(.Substitute(text_string, " ", "^",
> nth_word), 1, 256), _
> Find("^", .Substitute(text_string, " ", "^",
> nth_word)), 256)) - 2)
> ElseIf nth_word = "First" Then
> Get_Word = Left(text_string, .Find(" ", text_string) - 1)
> ElseIf nth_word = "Last" Then
> Get_Word = Mid(.Substitute(text_string, " ", "^",
> Len(text_string) - _
> Len(.Substitute(text_string, " ", ""))), .Find("^",
> Substitute(text_string, " ", "^", _
> Len(text_string) - Len(.Substitute(text_string, " ", ""))))
> + 1, 256)
> End If
> End With
>
> End Function
> ----------------------------------------------------------
>
> sub macro ()
>
> dim r as string
> For i = 1 to 10
> r = get_word("A" & i, 6)
> Next
> ..
>
> Is there anything wrong with the way I use this funtion?
>
> --
> betty77
> ------------------------------------------------------------------------
> betty77's Profile: http://www.excelforum.com/member.php...o&userid=37092
> View this thread: http://www.excelforum.com/showthread...hreadid=569338

--

Dave Peterson