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)
Explanation 

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.

How the formula works

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

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.