+ Reply to Thread
Results 1 to 12 of 12

Convert Column Number to Letters MS Excel 2007 update

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Dallas Tx
    MS-Off Ver
    Excel 2007
    Posts
    1

    Convert Column Number to Letters MS Excel 2007 update

    Example of what code does is as follows

    If Column number = 1 then A
    26 = Z
    27 = AA
    702 = ZZ
    703 = AAA
    729 = ABA
    etc...

    Thanks to royUK for first posting the Excel XP version of the code! With Excel 2007 a little additional coding was required to fully utilize all the additional column available

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: Convert Column Number to Letters MS Excel 2007 update

    Here is another way, I think...

    If the active cell is the target:

    Please Login or Register  to view this content.
    If the number is returned from another line of code, or a user input:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    Johannesburg
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Convert Column Number to Letters MS Excel 2007 update

    Hi

    With the above function Column number 832 returns "AF@" instead of "AFA"

    Any suggested solution for this?

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Convert Column Number to Letters MS Excel 2007 update

    Please Login or Register  to view this content.
    for instance
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Convert Column Number to Letters MS Excel 2007 update

    Simply
    Please Login or Register  to view this content.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Convert Column Number to Letters MS Excel 2007 update

    I reckon that would cause a problem if the active sheet is a chart

  7. #7
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: Convert Column Number to Letters MS Excel 2007 update

    Quote Originally Posted by BrianPanicco View Post
    Hi

    With the above function Column number 832 returns "AF@" instead of "AFA"

    Any suggested solution for this?
    I wonder why that does this for you. Column 832 is AEZ. However, when I ask for column 833, it returns "AFA" as expected
    I included a line that would go the the column referenced so you can see.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-07-2012
    Location
    Johannesburg
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Convert Column Number to Letters MS Excel 2007 update

    Thanks a million

  9. #9
    Registered User
    Join Date
    10-04-2012
    Location
    Sterling Heights, Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Convert Column Number to Letters MS Excel 2007 update

    In Excel 2010 one may have 16,384 columns. The following code will accomodate that many. A routine to test the code on a new book is included.
    Please Login or Register  to view this content.
    I got the basic first few lines from someplace on the internet. I appologize for not having complete attribution.
    Last edited by JosephP; 11-19-2012 at 04:52 PM. Reason: add code tags

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Convert Column Number to Letters MS Excel 2007 update

    that seems a little overcomplicated to me considering some of the previous answers ;-)

    note I have added code tags to your post in accordance with forum rules

  11. #11
    Registered User
    Join Date
    10-04-2012
    Location
    Sterling Heights, Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Convert Column Number to Letters MS Excel 2007 update

    They are two different ways of approaching the problem. The one I posted is a called subroutine and not a function with manual input. As someone pointed out in another post, in the other code certain columns may not convert properly. I included the test code to demonstarte the effectiveness across ALL columns up to 16384 (XFD).

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Convert Column Number to Letters MS Excel 2007 update

    at least three of the solutions work with any valid column number in any version of excel even if a future version has more columns and a sub could simply call the function ;-)
    I'm not trying to be unnecessarily critical-just pointing out that there are easier methods (personally I've never had a need for this anyway)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1