Results 1 to 2 of 2

Thread: Find Parent Folder From Given Folder / File Path

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Lightbulb Find Parent Folder From Given Folder / File Path

    This will give the path of the parent folder. The advantage of this formula is that you can pass the hierarchy level by just changing the last argument in the formula (highlighted in bold red)

    Array Formula (confirmed by pressing CTRL+SHIFT+ENTER, this formula is otherwise known as CSE formula)
    =LEFT(A1,LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),1))


    Example
    I:\Pictures\Wallpapers\World Over\Misc Pics\New Delhi.jpg
    the above formula will give
    I:\Pictures\Wallpapers\World Over\Misc Pics\

    Changing the argument to 2

    I:\Pictures\Wallpapers\World Over\
    3
    I:\Pictures\Wallpapers\

    so on and so forth.....
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Add to extract just the folder name

    =MID(A1,LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),2)+1,LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),1)-LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),2)-1) and this will give

    Misc Pics

    =MID(A1,LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),3)+1,LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),2)-LARGE((MID(A1,ROW(1:255),1)="\")*IFERROR(SEARCH("\",A1,ROW(1:255)),0),3)-1)

    World Over
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Save Worksheets As New File To Specific Folder
    By k0st4din in forum Excel Help
    Replies: 18
    Last Post: 06-08-2013, 04:24 PM
  2. Loop Through And Delete Multiple File Types In A Folder
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 03-30-2013, 04:47 PM
  3. Replies: 1
    Last Post: 02-14-2013, 12:09 PM
  4. List File name in folder to excel with images
    By Ryan_Bernal in forum Excel Help
    Replies: 2
    Last Post: 01-15-2013, 11:37 AM
  5. Loop through a folder and find word
    By k0st4din in forum Excel Help
    Replies: 7
    Last Post: 12-08-2012, 02:22 PM

Tags for this Thread

Posting Permissions

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