Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Finding the Parent Folder.
Written by Allen Wyatt (last updated March 22, 2025)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
=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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2226) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Parent Folder.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
If you use UNC paths in your links to external information, those paths may need to be changed at some point. You can ...
Discover MoreOpen a workbook that someone else is working on, and you won't be able to save your changes back into the same file. ...
Discover MoreYou can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-03-22 10:08:46
Jeff C
The dash in front of the 2 is not really a dash, it's a special character and needs to be replaced with a regular dash for this formula to work.
This LET will accomplish the same task and might be easier for some readers to follow.
=LET(
fullPath, CELL("filename"),
lastSlashPos, FIND(CHAR(1), SUBSTITUTE(fullPath, "\", CHAR(1), LEN(fullPath) - LEN(SUBSTITUTE(fullPath, "\", "")) - 1)),
bracketPos, FIND("[", fullPath),
MID(fullPath, lastSlashPos + 1, bracketPos - 2 - lastSlashPos)
)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments