Announcement

Collapse
No announcement yet.

Extract Text After Last Specified Character

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Text After Last Specified Character



    Hi,
    I have a cell in my worksheet with a fully qualified filename like 'D:\abc\Def\GHI\Jkl\xyz123.app

    I would like to extract only the xyz123.app. Obviously, the number of characters is going to vary based on the filename. The find and search functions appear to help locate the cell but not the substring in the text. The right and left appear to work based on number of characters and in my case these are varying. Also, there does not appear to be any function that can do a search a string from right to left.

    Can you please help with this?

    Thanks

  • #2
    Re: Extracting A Portion Of String From A Cell

    Use this custom Function;
    Code:
    Function PullAfterLast(rCell As Range, strLast As String)
        PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
    End Function
    Eg;

    =PullAfterLast(A1,"\")

    Comment


    • #3
      Re: Extracting A Portion Of String From A Cell

      Heres another way and Im not sure where I got this so credit to the orignal coder:

      Code:
       Function GetFileNameFromPath(ByVal strPath As String) As String
      ' Returns the rightmost characters of a string upto but not including the rightmost '\'
      ' e.g. 'c:\winnt\win.ini' returns 'win.ini'
          If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
              GetFileNameFromPath = GetFileNameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
          End If
      End Function
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        Re: Extract Text After Last Specified Character

        Dave, Reafidy

        Thanks for the prompt responses. Both your suggestions worked.

        Regards,

        mrangara

        Comment


        • #5
          Re: Extract Text After Last Specified Character

          Hi

          If you want to use a purely formula based approach then you could use the following:

          =RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

          Richard

          Comment


          • #6
            Re: Extract Text After Last Specified Character

            Parsnip,

            Thanks for the suggestion. It is an interesting approach and I actually used this with a slight mod to get the text prior to the last character.

            Regards,
            mrangara

            Comment


            • #7


              Re: Extract Text After Last Specified Character

              Topic closed, already twice been hijacked.
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment

              Working...
              X