Hmmmm, Yeah sure.

The quick and dirty way to do that is to wrap a mid string around it like you did above

I.e. Return the

=strPivotLocation(Sheet2!$A$3)

And assuming that formula is in cell A1 put this formula in another cell.

=MID(A1,FIND("]",A1)+1,FIND("!",A1)-FIND("]",A1)-1)

Alternatively, if you wanted to do all that in VBA then you can delete everything I posted before and replace it with just the following

Public Function strPivotLocation(rngRange As Range) As String
Dim strTemp As String, intI As Integer, pvtT As PivotTable
For Each pvtT In ThisWorkbook.Sheets(rngRange.Parent.Name).PivotTables
If (pvtT.TableRange1.Row = rngRange.Row) And (pvtT.TableRange1.Column = rngRange.Column) Then
strTemp = pvtT.PivotCache.SourceData
intI = InStr(1, strTemp, "]") + 1
strPivotLocation = Mid(strTemp, intI, InStr(1, strTemp, "!") - intI)
End If
Next pvtT
End Function

Comparing that to the original solution this is actually significantly simpler as you don't need to convert it to a proper range first and can just work with the text that is returned by PivotCache.SourceData.

*edit: Formatting and Clarity*

**Edited by galdarian, 08 May 2017 - 01:46 PM.**