Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filename from full path (Excel 2002/SP2)

    I have an Excel file with a column of filenames with full path. The paths are multiple levels deep, but not always the same number of levels deep.

    For Example:
    A4="d:tempdirAfile1.dat
    A5="d:tempfile2.dat
    A6="c:my_filestestdataotherdata.dat

    Is there an easy way to strip off only the filename? I figured I could use "=RIGHT", but I don't know how to find the last occurence of "".

    Thanks,
    Joe

  2. #2
    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: Filename from full path (Excel 2002/SP2)

    Try this megaformula:
    =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"",CHAR(1),(L EN(A1)-LEN(SUBSTITUTE(A1,"",""))))),LEN(A1))

    Steve

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

    Re: Filename from full path (Excel 2002/SP2)

    Or this array formula (confirm with Ctrl+Shift+Enter):

    =MID(A1,MAX((MID(A1,ROW(1:255),1)="")*ROW(1:255))+ 1,255)

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filename from full path (Excel 2002/SP2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    In VBA, there is a simpler way to derive the name of the file from the full path, than the ones Steve and Hans have proposed.

    <font color=blue>sFileName = DIR(fullPath)</font color=blue>

    would yield the name of the file. So maybe you may want to consider a User Defined Funtion. Not sure of performance issues, but its an alternative.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    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: Filename from full path (Excel 2002/SP2)

    If you are going to use VB, you can just use:

    activeworkbook.name
    or
    activesheet.parent.name
    or
    activecell.parent.parent.name

    Depending on what you want to feed to the UDF.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filename from full path (Excel 2002/SP2)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Steve

    From Joe's post
    For Example:
    A4="d:tempdirAfile1.dat
    A5="d:tempfile2.dat
    A6="c:my_filestestdataotherdata.dat
    End Joe's Post.

    Are Joe's .dat files example of Excel Workbooks?

    I took it to be any file of any type. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    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: Filename from full path (Excel 2002/SP2)

    Your way is probably more general.

    I assumed he had opened the DAT files (assuming they were a ascii file). Most of the DAT files I use are just TEXT files (though they might not be, but then *.txt files, *.prn, etc might not be text either.

    Steve

  8. #8
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename from full path (Excel 2002/SP2)

    Thanks to everyone for your suggestions. It's now up and running.
    BTW, the filenames in the cells are not worksheets or text files, but I don't need to open them. I only needed the names so that I could look a VLOOKUP from another table.

    Thanks again! [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename from full path (Excel 2002/SP2)

    Hans brilliant array formula is actually a normal formula which uses arrays. It doesn't need the Crtl-Shift-Enter, a normal enter will do!
    It's a great formula which really gets you thinking of further applications.

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

    Re: Filename from full path (Excel 2002/SP2)

    I got the idea from Bob Umlas' fascinating treatise on the use of array formulas in Using Array Formulas in Excel OFC-10.

Posting Permissions

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