I would like to extract the row number in a reference formula

formula in cell "= Bef22022007!F293"

How can I extract the 293?

Thanks
Hilton

Use the MID and FIND functions eg:
=MID(A1, FIND("!", A1)+2, 5)

where 5 is longer than the longest number of digits in the row numbers you
are expecting.
the +2 takes account of only a single-letter column reference, if you can't
be sure that is always the case then wrap the above is an
IF(ISERR(VALUE(MID(blah))), ,) to test whether you caught an extra letter.
If you have 2007, this would be a neater way to trap that error:
=IFERROR(VALUE(MID(A1,FIND("!",A1)+2,5)),VALUE(MID (A1,FIND("!",A1)+3,5)))
--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk


"Hilton" wrote:

I would like to extract the row number in a reference formula

formula in cell "= Bef22022007!F293"

How can I extract the 293?

Thanks
Hilton

=MID(A1,FIND("!",A1)+2,99)+0


"Hilton" wrote:

I would like to extract the row number in a reference formula

formula in cell "= Bef22022007!F293"

How can I extract the 293?

Thanks
Hilton

Hi,

I don't think I made it clear - this is formula in a cell, not a label. So
the function must pick up the fomula in the cell and then extract the row
referenced by the formula. The solution provided works when I make the
formula a label.


"Teethless mama" wrote in message
...
=MID(A1,FIND("!",A1)+2,99)+0


"Hilton" wrote:

I would like to extract the row number in a reference formula

formula in cell "= Bef22022007!F293"

How can I extract the 293?

Thanks
Hilton

You would need VBA for that or xl4, for a VBA solution see

http://www.mvps.org/dmcritchie/excel...htm#getformula


that will give you a string and then you could get the cell address using
MID or RIGHT


The only way one of Excel's regular functions will give you the cell is if
it is used like

=ADDRESS(ROW(Bef22022007!F293),COLUMN(Bef22022007! F293))

however if you put

=Bef22022007!F293

in A10 and wants the cell address then you would need the above VBA or one
of the GET functions from Excel4





"Hilton" wrote in message
...
Hi,

I don't think I made it clear - this is formula in a cell, not a label. So
the function must pick up the fomula in the cell and then extract the row
referenced by the formula. The solution provided works when I make the
formula a label.


"Teethless mama" wrote in
message
...
=MID(A1,FIND("!",A1)+2,99)+0


"Hilton" wrote:

I would like to extract the row number in a reference formula

formula in cell "= Bef22022007!F293"

How can I extract the 293?

Thanks
Hilton