Increment Letters (A+1 = B)
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Increment Letters (A+1 = B)

  1. #1
    Guest

    Default

     
    How do i increment the letters?
    I want to be able to select cells A1, then B2, then C3 etc.
    I can easily increment the rows, how do i increment columns?? thks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-20 06:50, Anonymous wrote:
    How do i increment the letters?
    I want to be able to select cells A1, then B2, then C3 etc.
    I can easily increment the rows, how do i increment columns?? thks
    =OFFSET(A1,1,1) is B2
    =OFFSET(A1,2,2) is C3

    ...therefore, the formula...

    =OFFSET($A$1,ROW(A1),ROW(A1))

    ...references B2, and as this formula is copied or filled down it references each sucessive diagonally adjacent cell (i.e., C3, D4, E5, etc.).


    [ This Message was edited by: Mark W. on 2002-02-20 07:57 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also

    =CHAR(65)

    returns "A", 66 is "B", and so on.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular sadi's Avatar
    Join Date
    Jun 2009
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    How to Increment Letters after "Z" like excel column (AA, AB, AC etc) is it Possible.

    Thanks

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    It is a lot better to work with numbers instead of letters to identify both rows and columns. That way column 26 is column Z and column 27 is column AA.

    Quote Originally Posted by sadi View Post
    How to Increment Letters after "Z" like excel column (AA, AB, AC etc) is it Possible.

    Thanks

  6. #6
    Board Regular boim's Avatar
    Join Date
    Dec 2009
    Location
    Indonesia
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    How to Increment Letters after "Z" like excel column (AA, AB, AC etc) is it Possible.
    something like this perhaps?

    Code:
    =ADDRESS(ROW(),COLUMN(Z1)+1)

  7. #7
    Board Regular sadi's Avatar
    Join Date
    Jun 2009
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    Thanks for reply

    Can i get like A, B, C.... Z Than AA, AB, AC Like Excel column

    Thanks again

  8. #8
    Board Regular boim's Avatar
    Join Date
    Dec 2009
    Location
    Indonesia
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    Code:
    =LEFT(RIGHT(ADDRESS(1,COLUMN($Z$1)+ your increment here,4),2),1)

  9. #9
    Board Regular sadi's Avatar
    Join Date
    Jun 2009
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    Thanks dear
    But i want Like this, if A1 = A than A2 =B .... =Z than AA, AB (Same as a excel Column name a to z than aa, ab..az)

    Thanks for reply

  10. #10
    Board Regular boim's Avatar
    Join Date
    Dec 2009
    Location
    Indonesia
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

      
    You meant "then" not "than" ?

    one possible way is:

    Code:
    =SUBSTITUTE(ADDRESS(1,COLUMN($A$1)+ B2,4),"1","")
    where B2 is the amount of increment.

    or put this from A1 down

    Code:
    =SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")
    Have you considered tusharm / Juan PG suggestions instead of trying whatever approach you're doing now?
    Last edited by boim; Feb 12th, 2010 at 05:39 AM.

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
  •  

 

 
DMCA.com