If you would like to get more tips
from The Excel Addict every week,
please click here.
The Excel Addict - Help with Excel 365, 2016, 2013, 2010, 2007, 2003
 
 
February 8, 2018
 
Hi fellow Excel Addict,
 
Today's 'Excel in Minutes' tip is 'Use Formulas To Return A Workbook's Path, Filename and Sheet Name'.

One practical use for the 'Sheet Name' part of this tip that I helped someone with just today is to extract a date from the sheet name and use it in a formula.
Date From Sheet Name Used In Formula in Microsoft Excel 2007 2010 2013 2016 365

If you find my tip it helpful, please be kind and share it with others who may also need a little help as well.

I hope you have a fantastic week

Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes - Learn to work smarter with Excel at TheExcelAddict.com
 
Start Your Free Trial Today!
 

If you missed my last newsletter, you can click here to view it online.
 

Quote of the Day

"Never look back unless you
are planning to go that way."

-- Henry David Thoreau --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.
 
Excel in Seconds with TheExcelAddict.com
 
Use Formulas To Return A Workbook's Path, Filename and Sheet Name

You may have wondered why there is an option in Excel to put the sheet name, workbook name, and even the entire path of a workbook into your header or footer but there is no option to display it in the worksheet itself. Me too.
Show Workbook Path Filename In Header Or Footer in Microsoft Excel 2007 2010 2013 2016 365

Thankfully, we can create formulas using a few functions to replicate this feature in our worksheets.

For the examples below I have created formulas in the Summary sheet of my workbook...
C:\Users\fhayes\Documents\04. Banking\2018 Budget.xlsx


To display the full path, file and sheet name of the current workbook:

Use this formula:
=CELL("filename",A1)

The result is:
C:\Users\fhayes\Documents\04. Banking\[2018 Budget.xlsx]Summary
 
Formula To Display Full Path Workbook File And Sheet Name in Microsoft Excel 2007 2010 2013 2016 365

You'll notice that the A1 reference in this formula is NOT the same as the cell where the formula is located. The cell reference that you use in these formulas doesn't matter as long as it is a cell in the same worksheet as the formula. I always use A1 because it is just easier to remember.


To show the workbook name only:

This formula uses the MID and FIND functions to locate the square brackets in the full path and filename and pull out just the filename.

Use this formula:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1, FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

The result is:
2018 Budget.xlsx
Formula To Display Only Workbook Name in Microsoft Excel 2007 2010 2013 2016 365


To show only the path of the workbook:

This formula uses the LEFT and FIND functions to locate the first square bracket in the full path and file name and pull out everything to the left of it.

Use this formula:
 =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The result is:
C:\Users\fhayes\Documents\04. Banking\
Formula To Display Only Workbook Path in Microsoft Excel 2007 2010 2013 2016 365


To show the name of the sheet tab:

This formula uses the RIGHT, LEN and FIND functions to locate the second square bracket in the full path and file name and pull out everything to the right of it.

Use this formula:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))- FIND("]",CELL("filename",A1),1))

The result is:
Summary
Formula To Display Only Sheet Tab Name in Microsoft Excel 2007 2010 2013 2016 365


Other worksheets and workbooks:

With these formulas, if you want, instead of A1, you can use a reference to another sheet tab, or even another workbook, to return the same information for that particular worksheet or workbook.

Understand how these formula work:

To see exactly how these formulas work, highlight a part of the formula and press the F9 key to see the result of just that part of the formula.
Press F9 To Calculate Part Of A Formula in Microsoft Excel 2007 2010 2013 2016 365


Using my formulas:
Instead of retype these formulas, you can simply copy mine and paste them into your Formula Bar in Excel.
 
Another option: VBA Code

If you are familiar with using macros, and would like to be able to easily insert the above formulas into any workbook, I have created a macro to help you.

1) Right-click here to open a plain text file containing the macro code;
2) Copy the code into your Personal Macro Workbook;
3) Add the macro to your Quick Access Toolbar;
4) Select the cell where you want to insert the path, file, or sheet name;
5) Click the macro button on your QAT to scroll through each of the formulas (i.e. full, file, path, sheet, blank)

 
If you've found this tip helpful, please share it.
 

If you would like to get more tips
from The Excel Addict every week,
please click here.
Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
 
"Spreadsheets Tips From An Excel Addict" is a twice-weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630