Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trim-Parse troubleshoot (2003)

    I'm trying to troubleshoot a piece of code which parses the contents of a column (E) (in reverse) to show the data on the right side of the "/" in column . If that char is not present, then the contents should be shown.
    Can one of you wiz's help me solve this without applying a UDF or VBA function?
    Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim-Parse troubleshoot (2003)

    Will there ever be more than 3 owners?

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    More than likely...all I want is the last one to the right. Is it necessary to count every instance? I saw a post which mentioned INSTRREV (or something like that). Is it just a VBA function?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim-Parse troubleshoot (2003)

    For a maximum of three owners:

    =IF(ISERROR(SEARCH("/",IF(ISERROR(SEARCH("/",C2)),C2,TRIM(MID(C2,SEARCH("/",C2)+1,1000))))),IF(ISERROR(SEARCH("/",C2)),C2,TRIM(MID(C2,SEARCH("/",C2)+1,1000))),TRIM(MID(IF(ISERROR(SEARCH("/",C2)),C2,TRIM(MID(C2,SEARCH("/",C2)+1,1000))),SEARCH("/",IF(ISERROR(SEARCH("/",C2)),C2,TRIM(MID(C2,SEARCH("/",C2)+1,1000))))+1,1000)))

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim-Parse troubleshoot (2003)

    With more than 3 owners you really need a VBA solution. INSTRREV is indeed a VBA function.

    Put this simple function in a standard module:

    Function ExtractLast(strValue As String) As String
    Dim intPos As Integer
    ExtractLast = Trim(Mid(strValue, InStrRev(strValue, "/") + 1))
    End Function

    And use like this

    =ExtractLast(C2)

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    AWESOME! That rocks! You rock. Saved me hours of work! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trim-Parse troubleshoot (2003)

    Just for info, here is a non-VBA approach:
    =IF(COUNTIF(C5,"*/*")=0,C5,TRIM(RIGHT(C5,LEN(C5)-SEARCH("~#@",SUBSTITUTE(C5,"/","~#@",LEN(C5)-LEN(SUBSTITUTE(C5,"/",""))))+1)))
    it assumes you will never have <code>~#@</code> as part of your text!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    Another awesome solution!
    Had I not had the ability to create or append a module, this would've saved my keester.
    Cheers to you too! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trim-Parse troubleshoot (2003)

    <hr>it assumes you will never have ~#@ as part of your text!<hr>
    You mean, no comic-book cursing?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trim-Parse troubleshoot (2003)

    it would be an impressive curse to require all three at once! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> (I'm pretty sure South Park must have come up with a suitable one...)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    Here's an array formula that will find the last "/" no matter how many owners. Enter using Ctrl-Shift plus Enter

    =RIGHT(C2,LEN(C2)-MAX(IF(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)="/",ROW(INDIRECT("1:"&LEN(C2))),0)))

    Ken
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    So what is the equivalent to the "search" function in VBA?

    I was messing around with this thread and tried to make a function that would do the following. The cells contain two columns of data that need to be separated. A cell contains "leftside rightside" with a space between the two vaules.

    Dim intLen As Integer
    Dim intPos As Integer
    intLen = Len(ActiveCell.Value)
    intPos = Search(" ", ActiveCell.Value)
    ActiveCell.Value = Right(ActiveCell.Value, intLen - intPos)
    ActiveCell.Offset(1, 0).Select

    This yields "Sub or function not defined" for "Search". The first question is how do I "search" in VBA? The second question is, is there a better way to take data like this and return one side or the other? I can create a cell formula and create a new column, but I wanted to create a macro that could be used to change the data in place.

    Thanks,

    Andy

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trim-Parse troubleshoot (2003)

    You can use <code>InStr</code> to locate text within other text, or you can use <code>Split</code> to create an array separating the elements at specified characters.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim-Parse troubleshoot (2003)

    Thanks Rory! Modified bit of code below:

    Dim intLen As Integer
    Dim intPos As Integer
    intLen = Len(ActiveCell.Value)
    intPos = InStr(ActiveCell.Value, " ")
    ActiveCell.Value = Right(ActiveCell.Value, intLen - intPos)
    ActiveCell.Offset(1, 0).Select

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim-Parse troubleshoot (2003)

    You could also use

    Dim intPos As Integer
    intPos = InStr(ActiveCell, " ")
    ActiveCell = Mid(ActiveCell, intPos + 1)
    ActiveCell.Offset(1, 0).Select

    It would also be possible to avoid selecting cells; this is usually more efficient, but we'd need to know more about how your code loops.

Posting Permissions

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