Getting the Cell ID
 

Hi,

I have got an Excel sheet in which text and numbers are there.
1) I want to get the ID of the cell with specified text. Is there any
function for doing that?
2) How can I find the last row with data in the same sheet.

Thanks,
Shaiju

Getting the Cell ID
 

Hi

Assuming your column containing the text is column A , and the text you were
looking for is Roger, then
=ADDRESS(MATCH("Roger",A:A,0),1)
The 1 at the end refers to column A.
adjust for other columns.

Finding the last row with data, will depend upon whether tit is Numeric or
Text.
For Numeric
=LOOKUP(99^99,A:A)
For Text(
=LOOKUP(REPT("Z",255),A:A)

In each case you are choosing some arbitrary value that is unlikely to occur
in the column.
I have chosen 99 to the power of 99.
Some people recommend using
9.99999999999999E+307
which is the largest number that can be written in an Excel cell, but I find
that cumbersome to remember and type. 99^99 has always more than covered the
values for me.
--
Regards
Roger Govier

wrote in message
...
Hi,

I have got an Excel sheet in which text and numbers are there.
1) I want to get the ID of the cell with specified text. Is there any
function for doing that?
2) How can I find the last row with data in the same sheet.

Thanks,
Shaiju

Getting the Cell ID
 

On Oct 28, 5:02*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

Assuming your column containing the text is column A , and the text you were
looking for is Roger, then
=ADDRESS(MATCH("Roger",A:A,0),1)
The 1 at the end refers to column A.
adjust for other columns.

Finding the last row with data, will depend upon whether tit is Numeric or
Text.
For Numeric
=LOOKUP(99^99,A:A)
For Text(
=LOOKUP(REPT("Z",255),A:A)

In each case you are choosing some arbitrary value that is unlikely to occur
in the column.
I have chosen 99 to the power of 99.
Some people recommend using
9.99999999999999E+307
which is the largest number that can be written in an Excel cell, but I find
that cumbersome to remember and type. 99^99 has always more than covered the
values for me.
--
Regards
Roger Govier

wrote in message

...



Hi,

I have got an Excel sheet in which text and numbers are there.
1) I want to get the ID of the cell with specified text. Is there any
function for doing that?
2) How can I find the last row with data in the same sheet.

Thanks,
Shaiju- Hide quoted text -

- Show quoted text -

Hi Roger,

Thank you very much.

Shaiju.