Extract the row number

  • Thread starter Thread starter Hilton
  • Start date Start date
H

Hilton

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)))
 
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.
 
You would need VBA for that or xl4, for a VBA solution see

http://www.mvps.org/dmcritchie/excel/formula.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
 
Back
Top