Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 Need some Excel cell formula help, to extract folder name

views
     
TStritonite
post Nov 18 2016, 04:40 PM, updated 8y ago

Casual
***
Junior Member
337 posts

Joined: Feb 2009
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.


imccw
post Dec 7 2016, 05:44 PM

New Member
*
Junior Member
17 posts

Joined: Sep 2013
QUOTE(tritonite @ Nov 18 2016, 04:40 PM)
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.
*
Don't really understand which one you need, is it something like below?
Attached Image

 

Change to:
| Lo-Fi Version
0.0112sec    0.22    6 queries    GZIP Disabled
Time is now: 29th March 2024 - 01:56 AM