Inserting the Workbook Name

Written by Allen Wyatt (last updated August 26, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

Chuck wonders if there is a way, using a formula, to add the workbook name into a cell. He would prefer it to just be the filename without the filename extension.

As is often the case, Excel provides several different ways that you can accomplish this task. The root of any approach relies upon the CELL worksheet function, as in the following formula:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".",CELL("filename"))-1-FIND("[",CELL("filename")))

This works because CELL("filename") returns a full pathname for the workbook, along with the sheet name. If, for example, you had a workbook named My Workbook.xlsx, then this is an example of what CELL("filename") could return:

/Users/user/Desktop/[My Workbook.xlsx]Sheet2

The rest of the formula is used to strip off everything up through the left bracket ([) and everything after the period that denotes the start of the filename extension. If you think you might have other periods in the full filename, then you could make one of your searches just a bit more specific:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,SEARCH(".xl",CELL("filename"))-1-FIND("[",CELL("filename")))

This searches for and strips everything starting with ".xl", which should cover all of your possible Excel workbooks. Notice, as well, that instead of using FIND to look for ".xl", this formula uses SEARCH. The reason is because FIND is case sensitive, but SEARCH is not.

You can shorten this formula considerably if you are using Microsoft 365 or Excel 2021. This example uses the LET function (available in those versions of Excel) to do the shortening:

=LET(f,CELL("filename"),b,FIND("[",f),MID(f,b+1,SEARCH(".xl",f)-1-b))

If you are using Microsoft 365, you can make the formula shorter still by using the TEXTBEFORE and TEXTAFTER functions, in this manner:

=TEXTBEFORE(TEXTAFTER(CELL("filename"),"["),".xl",,1)

The formula essentially says "use all the text before the period and after the left bracket." The thing to keep in mind with this approach, however, is that both TEXTBEFORE and TEXTAFTER are, by default, case sensitive. This is why the final parameter used in this formula (the 1) is included for the TEXTBEFORE function—it makes TEXTBEFORE pay no attention to case, so it will find ".xl" or ".XL" the same.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13432) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

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

Turning Off Automatic Hyphenation for Parts of a Document

Word can hyphenate documents automatically, if you want it to. But what about those situations where you want most of a ...

Discover More

Changing Portions of Many Hyperlinks

If you need to modify the URL used in a large number of hyperlinks, you can do so by using a macro and a little ...

Discover More

Entering Dates without Separators

When doing data entry into a worksheet, you might want to enter dates without the need to type the separators that are ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

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 ...

Discover More

Deriving High and Low Non-Zero Values

When analyzing your numeric data, you may need to figure out the largest and smallest numbers in a set of values. If you ...

Discover More

Throwing Out the Lowest Score

Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 one more than 7?

2023-08-27 12:34:28

J. Woolley

Please excuse the following "nitpicking" comments:
1. The formulas that use CELL("filename") will generally fail if the workbook is NEW and not previously saved; in this case CELL("filename") is blank ("").
2. Willy's UDF fails if the workbook is NEW and not previously saved; in this case, there is no "." in ActiveWorkbook.Name.
3. FindRev(".",NameOf("book")) in my previous comment returns error #VALUE! if the workbook is NEW and not previously saved; in this case, there is no "." in NameOf("book").
4. CELL("filename") returns the "wrong" result if another open workbook is active; in this case it returns the active workbook's filename (with path).
5. Willy's UDF returns the "wrong" result if another open workbook is active; in this case
    ActiveWorkbook.Name
should be replaced by
    Application.ThisCell.Parent.Parent.Name
6. If the workbook is saved with another name (File > Save As), Willy's UDF does not update after automatic recalc (or F9); in this case the UDF should begin with
    Application.Volatile
7. NameOf("book") in my previous comment returns the "correct" result if another open workbook is active or if the workbook is saved with another name.


2023-08-26 13:20:35

Willy Vanhaelen

Another way to accomplish this task is to use a User Defined Function (UDF):

Function WorkbookName()
WorkbookName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
End Function

To use it, enter =WorkbookName() in the cell of your choice.

When you put this UDF in your personal workbook you can use it in any workbook.


2023-08-26 13:01:44

J. Woolley

My Excel Toolbox includes the following function to return the substring of Text between two substrings BeginAfter and EndBefore:
=Between(Text,BeginAfter,EndBefore,[CaseSensitive],[Direction])
If Direction is -1, the search is right-to-left for both BeginAfter and EndBefore (locating EndBefore first, then BeginAfter). Therefore, this formula returns the workbook's name without its extension:
=Between(CELL("filename"),"[",".",,-1)
There are two more functions in My Excel Toolbox that are useful for this subject:
=FindRev(FindText,WithinText,[StartNum],[CaseSensitive])
=NameOf([This],[Target])
FindRev searches in reverse (right-to-left) to return the location of FindText in WithinText.
NameOf returns information about Target (a cell or range); default Target is the formula's cell. The first parameter This can be "sheet" (or "worksheet"), "book" (or "workbook"), "path" (or "filepath"), "app" (or "application"), "caption" (or "titlebar"), "statusbar", "user", "organization", "printer", "computer", "?" (or "help"), or the name of an environment variable (like "TEMP"); default is "sheet" (or "worksheet"). Using "book" (or "workbook"), the filename is returned without its path.
Therefore, this formula will return the workbook's name without its extension:
=LEFT(NameOf("book"),FindRev(".",NameOf("book"))-1)
Or with LET in Excel 2021+:
=LET(B,NameOf("book"),LEFT(B,(FindRev(".",B)-1)))
See https://sites.google.com/view/MyExcelToolbox


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.