A formula using the combination of CELL, FIND and MID functions in Excel can be used to return the Sheet Name into a cell.
Formula to return Sheet name
The following is the generic formula to return Sheet name in Excel.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Explanation
CELL function with “filename” as it’s first argument will return the file path of the Excel file along with the Sheet Name.
Here, F:\Data is the folder containing the Excel file called April.xlsx and Sales Data is the current sheet.
FIND function is then used to find the position of the ‘]‘ (closing Square bracket) in the file path returned by CELL function. In this case position of ] is 20.
This value is used in MID function to return the Sheet Name.
