Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding the Directory Name.

Finding the Directory Name

by Allen Wyatt
(last updated December 7, 2013)

6

If you have a need to find out the directory in which your workbook is saved, you may be tempted to use a macro to figure out the answer. While this is a valid approach (and relatively easy), some people are intimidated by macros or don't want to use them within the workbooks. The following worksheet formula will return the directory in which the workbook is stored:

=LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1)

If you use this formula in a workbook that is brand new—one that has yet to be saved—then it will return a #VALUE! error. This happens because the filename has not yet been set, and the LEFT function cannot return a portion of something that is not there. To avoid the error, simply encase the formula in the IFERROR function, as follows:

IFERROR(LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1),"")

In this variation the CELL function is used to extract and return the directory name, but only if it has been set. If it has not been set, then an error occurs and IFERROR returns an empty string.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9455) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding the Directory Name.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Restoring a Keyboard Shortcut

Word uses keyboard shortcuts for all sorts of tasks. Sometimes you may create a shortcut that messes up one of the other ...

Discover More

How Word Handles Abbreviations

Abbreviations appear all over the place in our society. If you want to understand how Word recognizes them (which it has to ...

Discover More

Converting UTC Times to Local Times

Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your local ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Generating Random Strings of Characters

If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this ...

Discover More

Listing Combinations

You can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given number ...

Discover More

Calculating the Median Age of a Group of People

Suppose you have a worksheet that contains a list of ages and then a count of people who correspond with those ages. You may ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2014-05-21 08:34:20

Natashia

Really Scott? Because I think it's kind of underhanded to do that. Flow has nothing to do with citing a worthy comment, as seen in scholarly articles anywhere. It sounds like that's how you meant it to be though, Allen. Especially since you deleted Bryan's comment showing the information he gave you. Your articles are informative, but I'm not sure I trust your credentials or morals if this is how you treat your tips.net comment section. I hope that articles I read here in the future are original or at least give credit where credit is due. It's not shameful to take constructive criticism and suggestions from others. People would respect you more for it in the end.

But that's all I'll say on the matter. Thank you for the tips and I hope to read more in the future.


2013-12-16 20:52:51

Scott Renz

Hi Allen,

I like the style of your articles and the way that they flow.

To me it would be annoying if you broke up that flow by pausing to give credit information.

I don't assume that everything you write all came out of your own head. I would pressume that you might use information people kindly added as comments to your
articles.

If I give such comments, I expect no credit for them. I am happy to see them passed along and being useful to people.


2013-12-10 18:42:41

awyatt

Wow. All I can say is wow.

Guess I'll write up a "terms of use" document, then. Never had a call for it in 16 years of doing this, but I guess I need to write one up now.

Sheesh.

Oh, and by the way: Your math is quite a bit off, on the high side. As in more than an order of magnitude. (I *wish* the reality were close to your numbers. I wish.)

-Allen


2013-12-10 15:47:54

Bryan

You have mentioned this once before, but that doesn't stop me from getting upset about it. I can't find any sort of policy that states that comments are yours to do with what you please, and you didn't ask if you could use the comment in the article, so I'm having a hard time seeing how it's not stolen. It was my idea that you are presenting as your own.

You may well have given credit in the premium newsletter, but all that means is only the people who paid 19-24 cents for this tip and happened to be subscribed this week got to see the attribution. Anyone who found the tip through the website, or who finds it in the future, will just see a clean slate article and have no way to know the entire thing isn't your idea.

The brass tax of the situation is this: I don't have any sort of legal case here -- IFERROR isn't exactly a novel concept and you can argue you would have come up with it eventually. And you might have. But the fact remains that since this was a premium tip, you sold my revision to other people, and I didn't get so much as a (public) mention. If conservatively 1% of your 43,000 subscribers are premium subscribers, then this tip netted you somewhere between $82 and $103. Considering you had 4 tips this week, surely ~$350 (plus advertising plus book sales) is enough to write a comment policy.

And, ironically, I'm not comfortable giving you my full name or email address. Don't need the bosses figuring out what I do at work all day :)


2013-12-09 13:53:39

awyatt

Bryan,

It wasn't stolen. I felt your comment was good enough to be incorporated into the actual tip. When I revisit tips for republishing them in the newsletters, I often do that. (We've had this discussion before, IIRC.)

Credit is always given *in the newsletter*. This particular tip ended up this week in ExcelTips Premium.

By the way, it would be very nice to have your full name (first and last) so that I can always credit you correctly. Please, if you don't mind, send it to me: allen-at-sharonparq.com.

-Allen


2013-12-09 12:15:36

Bryan

Credit goes to me for simplifying the second formula (in a comment since removed). I guess maybe I should stop giving advice if it's going to be stolen without recognition.

There were a couple other good recommendations as well:

----

Paul Kinnecom 17 Sep 2013, 01:14

If I want to know the directory where my spreadsheet is saved, I do this:

Click on the File tab (or Office Button)
Click on "Save As"
Read the directory path, and
Click on Cancel

----

Marc 13 Sep 2013, 14:14

You can also customize the Quick Access Toolbar adding the 'Document Location' to display the complete path of any excel you are working with. This way, you can copy the path from that window to quickly create a hyperlink in an email or anywhere else. Very handy.

----

Bryan 13 Sep 2013, 09:11

In Excel 2007 they introduced a new formula IFERROR. You can use it to reduce your second formula to =IFERROR(LEFT(CELL("Filename",$A$1),
FIND("[",CELL("Filename",$A$1))-1),"")

See also: http://excelribbon.tips.net/T011356_Adding_a_File_Path_and_Filename.html

----


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.