• Trim-Parse troubleshoot (2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Trim-Parse troubleshoot (2003)

    Author
    Topic
    WSjazman2k
    AskWoody Lounger
    April 3, 2007 at 7:18 pm #441284

    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

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      April 3, 2007 at 7:23 pm #1059202

      Will there ever be more than 3 owners?

      Reply | Quote
      • WSjazman2k
        AskWoody Lounger
        April 3, 2007 at 7:27 pm #1059204

        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?

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          April 3, 2007 at 7:32 pm #1059206

          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)

          Reply | Quote
          • WSjazman2k
            AskWoody Lounger
            April 3, 2007 at 7:37 pm #1059208

            AWESOME! That rocks! You rock. Saved me hours of work! bravo

            Reply | Quote
            • WSrory
              AskWoody Lounger
              April 4, 2007 at 10:24 am #1059275

              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 ~#@ as part of your text!

              Reply | Quote
            • WSjazman2k
              AskWoody Lounger
              April 4, 2007 at 4:19 pm #1059355

              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! bravo

              Reply | Quote
            • WSdcardno
              AskWoody Lounger
              April 4, 2007 at 4:56 pm #1059358

              [indent]

              it assumes you will never have ~#@ as part of your text!

              [/indent]
              You mean, no comic-book cursing?

              Reply | Quote
            • WSrory
              AskWoody Lounger
              April 4, 2007 at 8:16 pm #1059400

              it would be an impressive curse to require all three at once! evilgrin (I’m pretty sure South Park must have come up with a suitable one…)

              Reply | Quote
    • WSHansV
      AskWoody Lounger
      April 3, 2007 at 7:27 pm #1059205

      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)))

      evilgrin

      Reply | Quote
    • WSkjktoo
      AskWoody Lounger
      April 4, 2007 at 10:18 pm #1059427

      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

      Reply | Quote
      • WSajlacombe
        AskWoody Lounger
        April 5, 2007 at 1:48 pm #1059498

        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

        Reply | Quote
        • WSrory
          AskWoody Lounger
          April 5, 2007 at 1:53 pm #1059499

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

          Reply | Quote
          • WSajlacombe
            AskWoody Lounger
            April 6, 2007 at 3:07 am #1059603

            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

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              April 6, 2007 at 8:42 am #1059611

              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.

              Reply | Quote
    Viewing 2 reply threads
    Reply To: Trim-Parse troubleshoot (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.