Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Finding the Parent Folder

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.

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.

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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Locke Garmin    19 Aug 2015, 08:38
The formula in this tip has an error if you try to paste it directly from here. Where it says: "CELL("filename")) – 2 -" (on the third line) the first horizontal line or "dash" character is an "en dash" character (ascii 150) and it should be a "hyphen" character (ascii 45) since excel doesn't recognize the "en dash" in formulas.
Ashley    15 Jul 2015, 17:38
CELL("filename") can produce irregular results. Open two SAVED files (A and B) in two different directories.

In each file, put this formula:
=CELL("filename")

Now, making a change in file A will change the value of the formula in sheet B. This can be insidious to track down.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.