9

In column A I have 20000 rows with filename with file path

"C:\person\microsoft\ygkyg\mmddyy\filename.xls"
"\server-41\performance\mmddyy\filename.doc"
.....
etc.

In column B I just want to get the parent folder path.

Could someone help me with the formula? I tried this but it's giving me the file name.

=MID(a1,FIND(CHAR(1),
    SUBSTITUTE(a1,"\",CHAR(1),LEN(a1)-LEN(SUBSTITUTE(a1,"\",""))))+1,LEN(a1))
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Hannah Montana
  • 103
  • 1
  • 1
  • 4

1 Answers1

30

This works.

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))))

The above was my original answer. Neil simplified the expression somewhat and posted this as a comment below:

=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

This takes advantage of the fact that ? is a forbidden character in paths so that "?" can safely be used instead of CHAR(1) as a placemark, thus improving readability a little bit. Also, LEFT(A1,x) is equivalent to, and shorter than MID(A1,1,x), so it makes sense to use LEFT. But most importantly, this formula makes use of FIND, instead of a second layer of counting characters using LEN. This makes it much more readable.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • Missed by a few seconds, good one Jean! FY, the original discussion regarding reverse string lookup is here: http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba – Tiago Cardoso Mar 31 '11 at 19:20
  • One more quick thing. It's giving me C:\person\microsoft\ygkyg\mmddyy\ but I dont need the last "\" – Hannah Montana Mar 31 '11 at 19:42
  • 3
    Slightly simplified: `=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))` (note that this includes the trailing backslash) – Neil Mar 31 '11 at 19:42
  • =MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),LEN(A1)))) I took out the +1 and it took out the last "/" from the path. Thanks – Hannah Montana Mar 31 '11 at 19:43
  • 1
    To get the grandparent path using the shorter solution `=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))`, we need to understand it. It translates to "Count the number of backslashes in the path (LEN of path - LEN of path without backslashes), and replace the final one with a '?'. Then take all characters to the left of that '?'." So to get the grandparent path, you would replace the next earlier backslash, instead -- i.e. change `LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))` to `LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1`. – user3454591 Apr 02 '17 at 23:45