VBA String Manipulation - REPOST

[poleary2000]

All,

I have the following situation.

I have text with the following values:

2002.03.01!Monday2
2002.03.01!Thursday
2002.03.01!Friday
2002.03.01!SaturdayWO2
etc.

Is there anyway I can capture all information to the right of the ! and set it equal to a variable? I need everything right of the !. I have been trying Left and Right with no success.

I need to do this with VBA and not a cell on the worksheet.

[Mark O'Brien]

Here's a quick example with a couple of functions:

Public Sub main()

  MsgBox LeftString("2002.03.01!Monday2", "!")

  MsgBox RightString("2002.03.01!Monday2", "!")

End Sub



Public Function LeftString(ByVal sText As String, ByVal sSeparator As String) As String

  LeftString = Left(sText, InStr(1, sText, sSeparator) - 1)

End Function

Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String

  RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))

End Function

HTH

[poleary2000]

WOW! That works great! I'm not sure I completely understand it though.

[poleary2000]

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.

Any additional help you could lend would be very appreciated.

[poleary2000]

Sorry - I figured it out. Great call!!!

By the way, what does HTH mean?

[Mark O'Brien]

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.

Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String

  RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))

End Function

Basically what the line :

Public Function RightString(ByVal sText As String, ByVal sSeparator As String) As String

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:

  RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))

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:

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

If you've got any preoblems just repost.

[Mark O'Brien]

HTH = Hope This Helps

I picked that up from this message board. Initially it annoyed the sh!t out of me.

[poleary2000]

Mark,

THANK YOU very much for the complete explanation. You have been MOST helpful.

Thanks again,
Patrick