User Defined Functions: isnt that always the case?

**cardinal_pilot177** · 03-23-2011, 11:11 AM

First, this is my first post. I am in trouble and need help (isnt that always the case?)

I have 1000 lines in excel I need to go in and extract the 5th and 6th word within the text string.

Example
A Cell
1 Agent Summary: 20089: After Bill Willamson March 233 XRay
2 Agent Summary: 18977: After Randy Wi April 299 NA
3 Agent Summary: 122: REN Sue Nel October REACH NA

I googled what I was needing and found something I thought I could cut and past. Its giving me a error: "Compile Error, Expected Text or Binary"

I copied the UDF from http://www.ozgrid.com/News/jul-2005.htm

this is just a snap shot of what was copied - not all.

Option Compare TextFunction Get_Word(text_string As String, nth_word) As StringDim lWordCount As Long With Application

Any help would be much appreciated!

thank you

**jwright650** · 03-23-2011, 11:19 AM

can you post the code that you have written (don't forget to use [ code]....[ /code] tags around your code)

Might be able to pick out the line that is giving you trouble.

**cardinal_pilot177** · 03-23-2011, 11:25 AM

Thanks for looking

Code
[Option Compare TextFunction Get_Word(text_string As String, nth_word) As StringDim 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]

=Get_Word(A1,"Last")
would result in the word " " as that word is the last word.

=Get_Word(A1,"First")
would result in the word " " as that word is the 1st word.

=Get_Word(A1,6)
would result in the word " " as that word is the 6th word.

**jwright650** · 03-23-2011, 11:59 AM

Here try this....sorry for the wait.
I had to rewrite it, I could not make heads nor tails of it.....

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