Exceljet

Quick, clean, and to the point

Get workbook name only

Excel formula: Get workbook name only
Generic formula 
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
Summary 

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function.

Explanation 

The cell function is used to get the full file name and path:

CELL("filename",A1)

The result looks like this:

path[workbook.xlsm]sheetname

At the highest level, the MID function simply extracts the file name from the path and sheet. The starting position is calculated with FIND:

FIND("[",CELL("filename",A1))+1

The number of characters to extract is also calculated with FIND:

FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1
Author 
Dave Bruns
Related formulas 
Excel formula: Get full workbook name and path
Get full workbook name and path
The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. The type of information to be returned is specified by the info_type...
Get workbook name and path without sheet
The CELL function is used with "filename" to get the full workbook name and path: CELL ( "filename" , A1 ) The result is a full path that looks like this: path [ workbook.xlsm ] sheetname The LEFT function receives the full path as text,...
Related functions 
Excel CELL function
Excel CELL Function

The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the...

Excel MID function
Excel MID Function

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".

Excel FIND function
Excel FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

Good links 
Excel file and folder names (Chip Pearson)

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.