If your formula is always a reference with a sheet name at the beginning then
try this UDF:

Function rownummber(r As Range) As Long
Dim s As String, L As Long
rownummber = 0
If r.HasFormula Then
s = r.Formula
L = Len(s)
k = InStr(s, "!")
If k = 0 Then Exit Function
s = Right(s, L - k - 1)
If IsNumeric(s) Then
rownummber = CLng(s)
Else
rownummber = CLng(Right(s, Len(s) - 1))
End If
End If
End Function
--
Gary''s Student


"JohnJC" wrote:

This should be easy i am sure.

I want to get the row address out of a cell reference and into another cell.

EG given cell B2 which contains =sheet!a345 I would like to store 345 in C2.

Is there a formula which allows me to do this?

thanks in advance