Alex Campbell
asked on
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.WorksheetFunct ion
lWordCount = Len(text_string) - Len(.Substitute(text_strin g, " ", "")) + 1
If IsNumeric(nth_word) Then
nth_word = nth_word - 1
Get_Word = Mid(Mid(Mid(.Substitute(te xt_string, " ", "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
.Find(" ", Mid(Mid(.Substitute(text_s tring, " ", "^", 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_strin g, " ", "^", Len(text_string) - _
Len(.Substitute(text_strin g, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _
Len(text_string) - Len(.Substitute(text_strin g, " ", "")))) + 1, 256)
End If
End With
End Function
Option Compare Text
Function Get_Word(text_string As String, nth_word) As String
Dim lWordCount As Long
With Application.WorksheetFunct
lWordCount = Len(text_string) - Len(.Substitute(text_strin
If IsNumeric(nth_word) Then
nth_word = nth_word - 1
Get_Word = Mid(Mid(Mid(.Substitute(te
.Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
.Find(" ", Mid(Mid(.Substitute(text_s
.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_strin
Len(.Substitute(text_strin
Len(text_string) - Len(.Substitute(text_strin
End If
End With
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, great, thanks great solution
ASKER
Nth solution works well for me.
Open in new window