Thanks:  0
Likes:  0

# Thread: Increment Letters (A+1 = B)

1. ## 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).

2. ## Re: Increment Letters (A+1 = B)

Originally Posted by István Hirsch
Or try this:

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

3. ## Re: Increment Letters (A+1 = B)

Originally Posted by István Hirsch
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.

4. ## Re: Increment Letters (A+1 = B)

Originally Posted by 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).
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.

5. ## 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. ## Re: Increment Letters (A+1 = B)

Originally Posted by 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...
What should happen when you reach 11111-111-111ZZ and you drag it down one more time?

7. ## 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. ## Re: Increment Letters (A+1 = B)

Originally Posted by thearm
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)?

9. ## Re: Increment Letters (A+1 = B)

No, sorry. It will look like this:

31222-YYY-Z10ZA

10. ## Re: Increment Letters (A+1 = B)

Originally Posted by thearm
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),""),"")

## User Tag List

#### Posting Permissions

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