=SUBSTITUTE(MID(SUBSTITUTE(FORMULA(B2);"$";"");2;FIND(".";SUBSTITUTE(FORMULA(B2);"$";""))-2);" ";"")

If you can assure that there are no superfluous spaces and no $-character (absolute referencing) in front of the sheetname, you can use the much simpler formula: =MID(FORMULA(B2);2;FIND(".";FORMULA(B2))-2)  If, say, B2 is containing a formula just consisting of the equal sign and a reference into a different sheet, you can get the sheetname by standardfunctions as shown here:. here: =SUBSTITUTE(MID(SUBSTITUTE(FORMULA(B2);"$";"");2;FIND(".";SUBSTITUTE(FORMULA(B2);"$";""))-2);" ";"")  If you can assure that there are no superfluous spaces and no$-character (absolute referencing) in front of the sheetname, you can use the much simpler formula:
=MID(FORMULA(B2);2;FIND(".";FORMULA(B2))-2)