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
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.
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!
When you enter references to cells in a worksheet, using the Fill Handle to propagate the formulas to other cells can be ...
Discover MoreUsing Excel to maintain lists of information is not unusual. When working with the list you may need to determine how ...
Discover MoreExcel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments