show cell reference as text

[BEEJAY]

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

Function ws(rg As Range) As String
If InStr(1, rg.Formula, "!") > 0 Then
ws = Mid(rg.Formula, 2, InStr(1, rg.Formula, "!") - 2)
End If
End Function
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

 

[Joel]

There is a problem with th original code. It will only return a value if the
reference is on another worksheet. try this change

Function ws(rg As Range) As String
If InStr(1, rg.Formula, "!") > 0 Then
ws = Mid(rg.Formula, 2, InStr(1, rg.Formula, "!") - 2)
else
ws = Mid(rg.Formula, 2, len(rg.Formula))
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

This function seems to work also...

Function ws(rg As Range) As String
ws = Replace(Replace(Mid(rg.Formula, 1 + _
InStr(1, rg.Formula, "!")), "=", ""), "+", "")
End Function

Rick

 

[BEEJAY]

Joel:
Thanks for the prompt response.
However, I still can't get it to work.
As indicated before, Cell D3 still shows =ws(C3) and not G12, as expected
Any other ideas?

 

[BEEJAY]

Joel:
I don't know what I did, but just "bouncing" off and on Cell D3, and all of
a sudden it worked.
Rick R:
For education purposes I will yet work with your submission, as well.
Thank-you

Now for the next step. I hope you can help with this, as well.
In cell C13, I have entered: =OFFSET(INDIRECT,("D3"),,1).
It returns #NAME?. I presume it is somehow NOT picking up the cell
reference, out of Cell D3, namely G12. Do you have any magic to work on this
one?

 

[Rick Rothstein \(MVP - VB\)]

Rick R:

For education purposes I will yet work with your submission, as well.

If you have any questions about the code, feel free to ask.

Rick