VBA String Manipulation - REPOST

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Here's a quick example with a couple of functions:

<pre>
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</pre>

HTH
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
HTH = Hope This Helps

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

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

Thanks again,
Patrick
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top