The CELL function is used to get the full file name and path:
The result looks like this:
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:
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:
This requires more function calls, but works just as well.