WORKSHEETNAME

WORKSHEETNAME()
Returns the name of the worksheet in a given position.

REMARKS
??

'iSheetNo - The number of the worksheet you want the name of.

Public Function WORKSHEETNAME(Optional ByVal iSheetNo As Integer = -1) As String 

   If iSheetNo = -1 Then 
      WORKSHEETNAME = Application.ActiveSheet.Name 
   Else 
      WORKSHEETNAME = Application.Sheets(iSheetNo).Name 
   End If 

End Function 

You can use the following formula

=MID(CELL("filename",A1)), FIND("]",CELL("filename",A!)) + 1, 1000) 

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