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

Tables within Tables

Inserting a table in a document is easy. Did you know that you can also insert a table within another table? Word allows ...

Discover More

Adding Drop-Shadows to Paragraphs

Drop shadows are a style of paragraph border used to enhance the visual impact of a paragraph. They are also a great way ...

Discover More

Drop-Down List of Hyperlinks

Creating a drop-down list with Excel's data validation feature can be a nice touch for a worksheet. What if you want the ...

Discover More

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!

More ExcelTips (ribbon)

First Value Less Than or Equal to 100

If you need to evaluate a row of values to meet specific criteria, then you'll appreciate the discussion in this tip. It ...

Discover More

Producing an Array of Numbers

When working with arrays in a formula, it can be a bit confusing to understand how they work. In this tip I examine a ...

Discover More

Outlining Cells Referenced in a Formula

When you are editing a formula, Excel helpfully outlines the cells referenced in the formula. If you want this capability ...

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 2 + 2?

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.