Extracting the sheet name only (as a string) from a formula to another cell

[Mike H]

Hi,

ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in on the right. Close VB editor.

Back on the worksheet you call it like this

=GetText(A1)

Function GetText(frm As Range) As String
GetText = Replace(Replace(Left(frm.Formula, _
InStr(1, frm.Formula, "!", vbTextCompare) - 1), "'", ""), "=", "")
End Function