Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

Thread: Increment Letters (A+1 = B)

  1. #21
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,891
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

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

    @ukmikeb, bosco_yip, István Hirsch,

    Are you all replying to the question asked in Message #15? As I read that question, the OP does not want AA, AB, etc. to follow Z; rather he wants ZA, ZB, etc. to follow it (and then ZZA, ZZB, etc. to follow ZZ, and so on).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #22
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by István Hirsch View Post
    Or try this:

    =SUBSTITUTE(ADDRESS(1,ROW(),4),1,"")
    I see now there were similar solutions before.

  3. #23
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,891
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

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

    Quote Originally Posted by István Hirsch View Post
    I see now there were similar solutions before.
    I think that is because you (and the other recent posts) are not answering the latest question posed in this thread. See my comments in Message #21 for more on this.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #24
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Rick Rothstein View Post
    @ukmikeb, bosco_yip, István Hirsch,

    Are you all replying to the question asked in Message #15? As I read that question, the OP does not want AA, AB, etc. to follow Z; rather he wants ZA, ZB, etc. to follow it (and then ZZA, ZZB, etc. to follow ZZ, and so on).
    Rick

    In post #16 I realized the error that I had made in post #15 and satisfied the intermediate requirement.

    At that point I was pondering the final requirement which you have answered so economically.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  5. #25
    New Member
    Join Date
    Mar 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    How about this...

    If I have a number like 11111-111-111ZA in cell A1, I want to drag the fill handle so cell A2 says 11111-111-111ZB. Then cell A3 says 11111-111-111ZC ect...

    I tried to mess with the formula below but couldn't make it happen:

    =IF(RIGHT($a1,1)="Z", CHAR(CODE(LEFT(a1,1))+1),LEFT(a1,1))&CHAR(65+MOD(CODE(RIGHT(a1,1))+1-65,26))

    Thank you!

  6. #26
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,891
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

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

    Quote Originally Posted by thearm View Post
    How about this...

    If I have a number like 11111-111-111ZA in cell A1, I want to drag the fill handle so cell A2 says 11111-111-111ZB. Then cell A3 says 11111-111-111ZC ect...
    What should happen when you reach 11111-111-111ZZ and you drag it down one more time?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #27
    New Member
    Join Date
    Mar 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi there.

    Thanks for the response.

    It really doesn’t matter as long as I can get to ZZ. So I just need to go ZA to ZZ and then stop.

    Also, the characters will be more like 31222-YYY-Z10ZA - ZZ. Just wanted to mention it would be a mix of numbers and letters.

    Thanks!

  8. #28
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,891
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

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

    Quote Originally Posted by thearm View Post
    It really doesn’t matter as long as I can get to ZZ. So I just need to go ZA to ZZ and then stop.

    Also, the characters will be more like 31222-YYY-Z10ZA - ZZ. Just wanted to mention it would be a mix of numbers and letters.
    It is important to know the actual shape of your data (you should never simplify it for us). Is...

    31222-YYY-Z10ZA - ZZ

    the actual shape (with the space/dash/space before the last two characters)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #29
    New Member
    Join Date
    Mar 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    No, sorry. It will look like this:

    31222-YYY-Z10ZA
    Last edited by thearm; Feb 13th, 2019 at 04:04 PM.

  10. #30
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,891
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

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

    Quote Originally Posted by thearm View Post
    No, sorry. It will look like this:

    31222-YYY-Z10ZA
    I think this formula will do what you want...

    =IFERROR(IF(RIGHT(A1,2)<"ZZ",LEFT(A1,13)&LEFT(ADDRESS(1,COLUMN(INDIRECT(RIGHT(A1,2)&1))+1,4),2),""),"")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •