Column Letters

G

Guest

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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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!)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks for the replies, I've pasted in Aladin #2 (and will look to work out the script a little later..)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top