User Defined Functions: isnt that always the case?

[cardinal_pilot177]

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]

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]

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]

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

Please Login or Register  to view this content.

[cardinal_pilot177]

Runs great! Awesome.
I appreciate all the help.