Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract specific name from list (2002/SP3)

    Hi,

    I'm trying to figure out a formula to extract the last directory name from a column listing the full-path directory, and the formula needs to work in VBA.

    For example, in column A I have this list:

    C:My Downloads
    Cocuments and SettingsOwnerApplication Data
    cocuments and SettingsAll Users
    etc...

    In column B, I'd like to get the last directory from the list in column A, and without the "" sign. In this example this would be:
    My Downloads
    Application Data
    All Users

    How can I do this using a VBA formula?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific name from list (2002/SP3)

    You can use this UDF

    <code>
    Function getName(strDir As String, strChar As String)
    Dim i As Integer
    Dim Pos As Integer
    Dim subString As String

    For i = 1 To Len(strDir) - 1
    If Mid(strDir, i, 1) = strChar Then
    Pos = i
    End If

    Next i


    subString = Right(strDir, Len(strDir) - Pos)
    getName = Left(subString, Len(subString) - 1)


    End Function
    </code>

    Use it like this =getName(A1,"")
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract specific name from list (2002/SP3)

    How about this function usable in VB or excel:

    <pre>Option Explicit
    Function LastDir(sPath As String)
    Dim iNum As Integer
    Dim AWF As WorksheetFunction
    Dim sTemp As String
    Set AWF = Application.WorksheetFunction
    iNum = Len(sPath)
    sTemp = Left(sPath, iNum - 1)
    iNum = iNum - Len(AWF.Substitute(sTemp, "", "")) - 1
    sTemp = AWF.Substitute(sTemp, "", Chr(1), iNum)
    iNum = InStr(sTemp, Chr(1))
    LastDir = Mid(sTemp, iNum + 1)
    Set AWF = Nothing
    End Function</pre>


    Steve

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific name from list (2002/SP3)

    VBA is not nessesary
    =SUBSTITUTE(MID(A6,FIND(CHAR(5),SUBSTITUTE(A6&""," ",CHAR(5),MAX(1,LEN(A6)-LEN(SUBSTITUTE(A6,"",""))-1))),255),"","")

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific name from list (2002/SP3)

    A shorter formula way :

    =TRIM(MID(SUBSTITUTE(A1,"",REPT(" ",50)),(LEN(A1)-LEN(SUBSTITUTE(A1,"","")))*50-49,50))

    Regards
    Bosco

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific name from list (2002/SP3)

    Thanks all. Great help, great site!

Posting Permissions

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