Lawrence asked if there was a way to devise a formula that would return the name of the parent folder for the current workbook file. He wanted this to return just the folder name, and he wanted it to be derived using a regular Excel formula, not a macro or user-defined function.
The answer is, yes, it is possible to figure out the parent folder using a formula, but the formula is rather long and complicated. There were several examples of formulas submitted by readers; the following formula is the most concise:
=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)))
Please note that this is a real formula; it must appear on a single line in a cell.
The formula works by using the number of backslashes in the complete file path, and then replacing the second to the last slash with an ASCII value of 1. This value is then used as a “positioning aid” to help extract the parent folder’s name.
Note, as well, that you may get a #VALUE error until you save the workbook in which the formula is contained. (Until that point, there is no path to analyze, so the various functions in the formula return a #VALUE error.)
