Placing Col & Row identifiers into a cell as text

[John]

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?

 

[Pete_UK]

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

 

[Dave]

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

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

Regards - Dave

 

[Dave]

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