Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Column Letters

  1. #1
    Guest

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.




  3. #3
    Guest

    Default

    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!)

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try: -

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

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

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

    Try your formula in AB1.

    Aladin

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  8. #8
    Guest

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

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

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •