# Help With User Defined Function

#### anwaee2

##### Board Regular
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
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

##### Well-known Member
Rich (BB code):
``````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.

##### Active Member
user defined function to take the second to last word in a string and add 1 to it .
Code:
``````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

##### MrExcel MVP, Moderator
Maybe
Code:
``````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
Code:
``````Function anwaee2(sw As String) As String
Dim sp As Variant
sp = Split(sw)
anwaee2 = sp(UBound(sp) - 1) & 1
End Function``````

#### anwaee2

##### Board Regular
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.

1,095,178
Messages
5,442,844
Members
405,201
Latest member
kashyap44

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...