FORMULA to display parent folder name

[ms500716]

Does anyone have ideas for a FORMULA (not a macro) that can be used to diplay the parent folder name in a cell? I don't need the file name or workbook name, just the folder name.

Example,
My Workbook is stored in the following location: C:\Files\ExcelFiles

I want a fromula to return "ExcelFiles", since that is the parent folder

I have searched previous threads but found no resoultion.

 

[texasalynn]

try this:

=MID(A17,FIND("|",SUBSTITUTE(A17,"\","|",2))+1,FIND("[",A17)-1-FIND("|",SUBSTITUTE(A17,"\","|",2))-1)

but I think this will only work when the path is two layers deep

 

[ms500716]

I'm not sure what part of this isn't working, but it isn't. I get a #Value error.

BTW, my acutal path is 7 layers deep

\\folder1\folder2\folder3\folder4\folder5\folder6\folder7\filename.xls

 

[texasalynn]

oops - for testing I had the filename in cell a17
so if you put into a cell the formula
=cell("filename")
does it show 7 "\"?

=MID(CELL("Filename"),FIND("|",SUBSTITUTE(CELL("filename"),"\","|",2))+1,FIND("[",CELL("filename"))-1-FIND("|",SUBSTITUTE(CELL("Filename"),"\","|",2))-1)

 

[Blade Hunter]

Maybe use a UDF

Put this in a sub attached to the sheet:

Function FileFolder()
FileFolder = ActiveWorkbook.Path
End Function

Then in the workbook put:

=filefolder()

 

[ms500716]

The reason I want to use a formula is because the users accessing the file have high macro security that can't be changed.

I found a formula online, but it has an error in it somewhere. Can anyone tell where the error is?

=MID(CELL("filename"),FIND(CHAR(1),SUBSTITUTE(CELL("filename"),
"\", CHAR(1), LEN(CELL("filename")) - LEN(SUBSTITUTE(CELL("filename"),
"\", "")) - 1)) + 1, FIND("[", CELL("filename")) – 2 - FIND(CHAR(1),
SUBSTITUTE(CELL("filename"), "\", CHAR(1), LEN(CELL("filename")) -
LEN(SUBSTITUTE(CELL("filename"), "\", "")) - 1)))

 

[Blade Hunter]

How about this??

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

 

[texasalynn]

=MID(CELL("filename"),FIND(CHAR(1),SUBSTITUTE(CELL("filename"),
"\", CHAR(1), LEN(CELL("filename")) - LEN(SUBSTITUTE(CELL("filename"),
"\", "")) - 1)) + 1, FIND("[", CELL("filename"))-2 - FIND(CHAR(1),
SUBSTITUTE(CELL("filename"), "\", CHAR(1), LEN(CELL("filename")) -
LEN(SUBSTITUTE(CELL("filename"), "\", "")) - 1)))

the -2 needing to be fixed

 

[Blade Hunter]

Sorry didn't see the part about the last dir only, Try this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-2),"\",REPT(" ",100)),100))

It works by getting the DIR and replacing all "\" with 100 spaces. It then grabs the right 100 chars which will be a bunch of spaces and the final DIR name then it trims off the preceding spaces.

 

[Scott Huish]

You should always reference CELL("FILENAME") with a cell reference from the workbook in question, otherwise you could inadvertently return the value from a different workbook that was worked on.

=CELL("FILENAME",A1)


To see this create two blank workbooks and save both of them
In cell A1 of each workbook, put =CELL("filename")
Then change something in the second workbook, go back to the first sheet, you will see that it no longer references the current workbook but the last one that changed.