Why doesn't this function should extract nth word from cell?

This code is supposed to extract out the nth word from a text string, but I am not getting the expected result.

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
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
That looks overly complicated. Use this function instead...

Function Get_Nth_Word(text_string As String, nth_word As Integer) As String
    Get_Nth_Word = Split(text_string, " ")(nth_word - 1)    
End Function

Open in new window

0
 
Wayne Taylor (webtubbs)Commented:
To handle "First" and "Last", use this...

Function Get_Nth_Word(text_string As String, nth_word As Variant) As String

    Select Case nth_word
        Case "First"
            Get_Nth_Word = Split(text_string, " ")(0)
        Case "Last"
            Get_Nth_Word = Split(text_string, " ")(UBound(Split(text_string, " ")))
        Case Else
            Get_Nth_Word = Split(text_string, " ")(nth_word - 1)
    End Select
    
End Function

Open in new window

0
 
Alex CampbellAuthor Commented:
Wow, great, thanks great solution
0
 
Alex CampbellAuthor Commented:
Nth solution works well for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.