Page 1 of 3 123 LastLast
Results 1 to 10 of 24
Like Tree1Likes

Convert column number to letter

This is a discussion on Convert column number to letter within the Excel Questions forums, part of the Question Forums category; I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number ...

  1. #1
    New Member
    Join Date
    Jan 2004
    Posts
    9

    Default Convert column number to letter

    I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
    eg. somefunction(1) gives me column(A) as an answer?

    Thanks.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,200

    Default Re: Convert column number to letter

    =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

    =SUBSTITUTE(ADDRESS(1,COLUMN(A2),4),"1","")

  3. #3
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    hi!
    good only for column A to Z

    =CHAR(64+COLUMN(A2))
    There is always a better way!!

  4. #4
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Convert column number to letter

    Aladin's code works fine...

    Address function works as Address(row,column) to return an absolute reference

    eg

    Address(1,1) = $A$1

    All you need to do is ADD the number of columns you want to the column reference

    ie

    =SUBSTITUTE(ADDRESS(1,COLUMN()+64,4),"1","")

    and it will return BM.

    Nice Aladin as per usual

  5. #5
    New Member
    Join Date
    Jan 2004
    Posts
    9

    Default Re: Convert column number to letter

    Thanks for the help guys. I initially wanted to do this so I could move along the column putting a header in each populated column as i thought Range("A1") was the only way. Since you were asking why I realised there must be another way and now I am using cells(row,col).

    Thanks again.

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    625

    Default Re: Convert column number to letter

    Are you using this formula then

    =SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(B1),"")

    in say Cell B4 to get the result "B"

  7. #7
    New Member
    Join Date
    Jan 2004
    Posts
    9

    Default Re: Convert column number to letter

    No I have just set up a for loop that goes through all my columns inserting a number until it gets to the last one. eg.

    For x=1 TO NumOfCols
    Cells(1,x).value = x


    Something like that
    I dont have the code with me.

  8. #8
    Board Regular
    Join Date
    Aug 2005
    Posts
    2,878

    Default

    =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

    Could I get someone to explain the following portion of the formula/function and how it is converting the arguments from Aladin's formula?
    COLUMN(),4),"1","")

    I see Address is using 1 as the row reference.
    Column() w/out any argument and then 4 as an abs_num is the first part of the formula I do not understand.

    Thanks,

    ds

  9. #9
    Board Regular PA HS Teacher's Avatar
    Join Date
    Jul 2004
    Location
    Rochester, NY
    Posts
    2,838

    Default

    Doug,
    Address(rownumber,columnnumber) returns a string with the address of the cell equivalent of ActiveSheet.Cells(rownumber,ColumnNumber). The thir argument in the address function forces the Address function to return a string representing a relative reference.

    For example,
    =Address(1,2,4) would return "B1"

    Because the rownumber fed to the address function in this case is always 1, the substitute function is used to replace 1 with "", leaving the column Letter(s).

    [Edit]
    =Column() returns the column number of the hose cell, so
    =Address(1,2,4) would return a string containing the relative reference of the first cell in the column of the host cell.
    Shankspony likes this.
    Recent Draftee
    www.mrexcel.com/board2/viewtopic.php?t=199272
    Recently came across a Free 1 GB Storage/File Sharing Site www.box.net

  10. #10
    Board Regular
    Join Date
    Aug 2005
    Posts
    2,878

    Default

    Thanks PA,
    That helped explain perfectly.

    Cheers,

    Doug
    my site: www.ecboardco.com
    was built w/ a tremendous amount of help and guidance from mrexcel and a few dedicated board members: A Huge Thanks!

Page 1 of 3 123 LastLast

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
  •  


DMCA.com