Exceljet

Quick, clean, and to the point

Get sheet name only

Excel formula: Get sheet name only
Generic formula 
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Explanation 

To get the name of the current worksheet  (i.e. current tab) you can use a formula based on the CELL function.  CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the sheet name.  In the example shown, the formula in E5 is:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

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

CELL returns this result to the MID function as the text argument. The sheet name begins just after the left bracket, so the starting position is calculated with FIND:

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

The number of characters to extract is hardcoded as 255. In the Excel UI, you can't name a worksheet longer than 31 characters, but the file format itself permits worksheet names up to 255 characters, so this ensures the entire name is retrieved.

Alternative with RIGHT

You can also use the RIGHT function to extract the sheet name, instead of MID:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

This requires more function calls, but works just as well.

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.