CODEWITHSUNDEEP
excelworksheet-function
Hannah Montana
Hannah Montana

Reputation: 103

Get parent folder path from file path using cell formula

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

Upvotes: 9

Views: 42200

Answers (3)

Boohoolean
Boohoolean

Reputation: 25

I don't have enough reputation to comment on bendataclear's solution for Excel 2019/365, but if you don't have the file path in cell A1, you can use this formula:

=TEXTBEFORE(CELL("filename"),"\",-1)

Upvotes: 1

bendataclear
bendataclear

Reputation: 3848

For those coming to this problem in 2024, there's a much easier method available in Excel 2019/365 onward:

=TEXTBEFORE(A1,"\",-1)

Upvotes: 3

Jean-François Corbett
Jean-François Corbett

Reputation: 38551

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.

Upvotes: 31

Related Questions