Increment Letters (A+1 = B)

[Guest]

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

 

[Rick Rothstein]

@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).

 

[István Hirsch]

Or try this:

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

I see now there were similar solutions before.

 

[Rick Rothstein]

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.

 

[ukmikeb]

@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.

 

[thearm]

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!

 

[Rick Rothstein]

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?

 

[thearm]

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!

 

[Rick Rothstein]

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

 

[thearm]

No, sorry. It will look like this:

31222-YYY-Z10ZA

 

[Rick Rothstein]

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),""),"")