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