Hi all,
I need some Excel cell formula help to extract the grandparent folder name of a file in a worksheet WITHOUT resorting to VBA. Long story short being I need the file saved in .xlsx format, rather than .xlsm.
I know the following will return the complete path
=CELL("filename")
and the following will return the parent folder name
=TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-2),"\",REPT(" ",100)),100))
However, I can't work out a cell formula to return the grandparent folder name - i.e. the parent folder of the parent folder.
If anyone can help, it will be most appreciated.
Since some of the files in the server can be nested 5-6 folders deep, I would ideally like a generic cell formula which can extract the folder names recursive given a numeric value - i.e. '1' for the parent folder, '2' for the grandparent folder, and so on.
Failing that, a standalone cell formula will be fine.
Thanks.
Need some Excel cell formula help, to extract folder name