Seems to work fine. I had to put the periods before three Finds and one
Substitute.

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?
>

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200608/1