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

Unwanted Numbering on Pasted Tables

When pasting text from another document or from the Web you can have unexpected characters sometimes show up. Many of ...

Discover More

Extracting Pictures from a Document

Word allows you to easily insert pictures into your documents. Getting the pictures back out of your documents can be ...

Discover More

Catching Single-Letter Spelling Errors

There have been times when I've reviewed my writing and found lots of "lone letters," detached by a single space from the ...

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)

Incrementing References by Multiples when Copying Formulas

You can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied ...

Discover More

Adjusting Formulas for Top-Added Rows

Formulas are the heart of using Excel, and formulas often refer to ranges of cells. How you insert cells into the ...

Discover More

Shortcut for Viewing Formulas

If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard ...

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 four less than 6?

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.