Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once.
Use following formula –
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")
Make sure that A1 is used in the formula. If it is not used, it will extract sheet name for the last active sheet which may not be one which we want.
If you want the sheet name for last active sheet only, then formula would become
=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")