On 2002-04-09 08:49, poleary2000 wrote:
I have a variable named TextToSplit. The variable can change to be anything. It usually looks like:
2002.03.01!MondayWO2
2002.04.02!SaturdayPerDay
2002.04.23!ThursdayTime
SundayWO1
FridayTime
I need to capture everything to the right of the "!" [if the "!" is present] and assign it to a new variable call SplitText. If there is no "!" present, then leave the string alone. I can not determine from your recommendation how to do this. Basically, I have never used the Public Function.
Functions are basically just fancy subroutines that return a specific result. Basically, they allow the user to create their own functions just like the "built in" VBA functions like "Left$" etc.
Anyway, I'll explain the "RightString" function.
<pre>
Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String
RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))
End Function</pre>
Basically what the line :
<pre>Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String</pre>
is saying is that the Function will return a string value to the user. The function requires 2 arguments, sText and sSeparator.
sText is the string that we want to find the "!".
sSeparator is the text we want to use to separate sText, in this case the exclamation mark (!).
This line of code:
<pre>
RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))</pre>
Is going to set RightString equal to a new string based upon our manipulations.
There are three built in functions used here, I would suggest looking at VBA help for them, that will explain better than I can right here, look for:
Right
Len
InStr
Now, finally, to make this applicable to your problem. In your subroutine that you set "TextToSplit" you will put something like this:
<pre>
Public Sub YourSub()
'You've already set TextToSplit to be equal to something like "2002.03.01!MondayWO2"
if Instr(TextToSplit, "!") > 0 then
SplitText = RightString(TextToSplit, "!")
else
SplitText = TextToSplit
end if
End Sub</pre>
If you've got any preoblems just repost.