Help With User Defined Function

[anwaee2]

I haver a user defined function to take the second to last word in a string and add 1 to it. When I run the macro with the function in it, I get "Sub Or Function Not Defined". When I go to the debugger, the "Rept" function is highlighted.
User Defined Function:

Public Function SECONDWORD(sw As String) As String
ln = Trim(Left(Right(" " & Substitute(Trim(sw), " ", Rept(" ", 60)), 120), 60))+1
End Function

Thanks in advance for any help I may get.

 

[Fluff]

Neither Rept, nor Substitute exist in VBA.
Also that function won't return anything.

Can you please post a few examples of your data & what you want it to look like?

 

[WassimN]

Hello anwaee2

If what you need to is find the last word in a string, that is the word to the right of the last space character in a string then why can't you simply use:

RIGHT( text, [number_of_characters] )
Where number_of_Characters can be the position of the last space +1? The + 1 is to get to the character that starts the last word.

Would that not work in your strings?

 

[joeu2004]

Public Function SECONDWORD(sw As String) As String
Dim wf As Variant
Set wf = WorksheetFunction
SECONDWORD = Trim(Left(Right(" " & wf.Substitute(Trim(sw), " ", wf.Rept(" ", 60)), 120), 60))  ' no +1
End Function

I have not tested that extensively, but it does return "second" when the Excel formula is =secondword("first second third").

Note: The VBA alternatives to wf.Substitute and wf.Rept are Replace and String. I did not bother to see how they might work (or not) with your design.

 

[mohadin]

user defined function to take the second to last word in a string and add 1 to it .

Public Function SECONDWORD(sw As String) As String
Dim x As Variant
    x = Split(sw, " "): x(0) = 1:  x = Join(x)
    SECONDWORD = x
End Function

 

[Fluff]

Maybe

Function anwaee2(sw As String) As String
   Dim sp As Variant
   sp = Split(sw)
   sp(UBound(sp) - 1) = sp(UBound(sp) - 1) & 1
   anwaee2 = Join(sp, " ")
End Function

or

Function anwaee2(sw As String) As String
   Dim sp As Variant
   sp = Split(sw)
   anwaee2 = sp(UBound(sp) - 1) & 1
End Function

 

[anwaee2]

joeu2004, thank you so much. I have googled and searched through 3 books i have with no results. But your code worked perfectly. I have not tried the others yet but wish to thank all for the replies. Thanks again to all.