Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VBA String Manipulation - REPOST

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Thanks in advance,
    Patrick

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WOW! That works great! I'm not sure I completely understand it though.
    Thanks in advance,
    Patrick

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Thanks in advance,
    Patrick

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    By the way, what does HTH mean?
    Thanks in advance,
    Patrick

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HTH = Hope This Helps

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

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,

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

    Thanks again,
    Patrick

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •