Placing Col & Row identifiers into a cell as text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 28 May 2008 00:54 GMT

How do I get the Col letter and Row number of a cell (without the page name)
to appear as text in another cell?

Using the =CELL("address",xxx) and clicking on another page for the xxx cell
places the entire [workbook name]sheetname!$col$row in the cell. I only want
the col letter and the row #. How do I get only these?

Reply to this Message

Pete_UK - 28 May 2008 01:34 GMT

Here's one way:

=RIGHT(CELL("address",Sheet2!xxx),LEN(CELL("address",Sheet2!xxx))-
FIND("!",CELL("address",Sheet2!xxx)))

where xxx is the cell reference. If you do not want $ symbols, you can
use SUBSTITUTE around the formula to change them to blanks.

Hope this helps.

Pete

> How do I get the Col letter and Row number of a cell (without the page name)
> to appear as text in another cell?
>
> Using the =CELL("address",xxx) and clicking on another page for the xxx cell
> places the entire [workbook name]sheetname!$col$row in the cell. I only want
> the col letter and the row #. How do I get only these?

Reply to this Message

Dave - 28 May 2008 01:43 GMT

Hi John,
Try this:
=RIGHT(CELL("address",Sheet2!A1),4)

This gives the last 4 characters, which eliminates the unwanted stuff.

Regards - Dave

Reply to this Message

Dave - 28 May 2008 01:46 GMT

Oops,
UK Pete's answer is better. Mine doesn't work above row 9 or column Z
Regards - Dave

Reply to this Message