Get parent folder path from file path using cell formula

excel worksheet-function
Hannah Montana picture Hannah Montana · Mar 31, 2011 · Viewed 32.6k times · Source

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))

Answer

Jean-François Corbett picture Jean-François Corbett · Mar 31, 2011

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.

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More