Column Letters

[Guest]

Hi, I'm running some long functions which overhang the formula bar and obscure the column header letters. I've been re-entering the column letters manually into row 10 but have to change them each time my layout changes. Using =COLUMN()shows the column as a number - is there an equivalent function that will show the column as a letter?

 

[Aladin Akyurek]

On 2002-03-14 01:42, Anonymous wrote:
Hi, I'm running some long functions which overhang the formula bar and obscure the column header letters. I've been re-entering the column letters manually into row 10 but have to change them each time my layout changes. Using =COLUMN()shows the column as a number - is there an equivalent function that will show the column as a letter?

Not sure, but maybe you're looking for:

=ADDRESS(ROW(),COLUMN())

=CELL("address")

Otherwise please elaborate with examples.

 

[Guest]

Thanks Aladin, both give me $J$10 in cell J10 when I just want J

ie I'm just looking to display the column letter in a cell, A in A10, B in B10 etc

(to keep on top of my evermoreconfusing formulae!)

 

[Aladin Akyurek]

On 2002-03-14 04:50, Anonymous wrote:
Thanks Aladin, both give me $J$10 in cell J10 when I just want J

ie I'm just looking to display the column letter in a cell, A in A10, B in B10 etc

(to keep on top of my evermoreconfusing formulae!)

=MID(CELL("address"),2,SEARCH("@",SUBSTITUTE(CELL("address"),"$","@",2))-2)

Aladin

 

[Mudface]

Try: -

=LEFT(ADDRESS(1,COLUMN(),4),1)

 

[Aladin Akyurek]

On 2002-03-14 05:04, Mudface wrote:
Try: -

=LEFT(ADDRESS(1,COLUMN(),4),1)

Mudface,

Try your formula in AB1.

Aladin

 

[Aladin Akyurek]

On 2002-03-14 05:23, Aladin Akyurek wrote:

On 2002-03-14 05:04, Mudface wrote:
Try: -

=LEFT(ADDRESS(1,COLUMN(),4),1)

Mudface,

Try your formula in AB1.

Aladin

The behavior of CELL("address") is peculiar & interesting. But that doesn't help the OP.

So I propose using:

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("@",SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$","@",2))-2)

Aladin

 

[Guest]

Thanks for the replies, I've pasted in Aladin #2 (and will look to work out the script a little later..)

 

[Mudface]

You're right of course, Aladin .

Incidentally, I came up with a formula (below) which seems to work OK, is there a reason for doing it the way you did?

=MID(ADDRESS(1,COLUMN()),2,SEARCH("$",ADDRESS(1,COLUMN(),2))-1)

 

[Aladin Akyurek]

On 2002-03-14 11:11, Mudface wrote:
You're right of course, Aladin /board/images/smiles/icon_smile.gif.

Incidentally, I came up with a formula (below) which seems to work OK, is there a reason for doing it the way you did?

=MID(ADDRESS(1,COLUMN()),2,SEARCH("$",ADDRESS(1,COLUMN(),2))-1)

That simplifies nicely what the structure of my #2 implies. The ROW() bit is the most intolerable part, though. Good work.

Aladin