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

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.

Tags: excelexcel, file