10

I have strings in Excel like AA or XA.

I need to increment them like this:

For AA in cell A1, it will be AB in cell B1, AC in cell B2 and so on.

For XA in cell A1, it will be XB in cell B1, XC in cell B2 and so on.

I tried the popular code =CHAR(CODE(A1)+1) but it does not work after Z.

Any hints are welcome.

  • Does not work after Z? Do you mean after AZ or ZZ – Dave Jan 22 '15 at 11:10
  • Hi Dave, I've tried AB and it has been looking for B since. – Andy K Jan 22 '15 at 11:11
  • If this has anything to do with the way Excel refers to Cells, consider avoiding it by working with RC-notation: File > Options > Formulas >R1C1 reference style – Dirk Horsten Jan 22 '15 at 12:11
  • Hi Dirk, many thanks for the advice. However why? – Andy K Jan 22 '15 at 12:14
  • 1
    If you were planning to use the resulting strings in an INDIRECT formula for example, then @DirkHorsten's comment might be worth noting. – CallumDA Jan 22 '15 at 12:24

11 Answers 11

11

Try this: put "AA" into cell A1 and enter the following formula into cell B1 and drag across

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

It will increment as follows: AA, AB, AC,..., AZ, BA, BB, BC.... etc

You might want to adapt this formula to suit your particular presentation. Please note that this won't work past "ZZ".

Update: fixed bug

| improve this answer | |
  • 1
    =IF(RIGHT(A1)="Z",CHAR(CODE(LEFT(A1))+1),LEFT(A1))&IF(RIGHT(A1)<>"Z",CHAR(CODE(RIGHT(A1))+1),CHAR(65)) – user26631 Jan 14 '16 at 16:01
3

We can use the excel spreadsheet itself to help increment the letters - the increment will work from A to XFC

First create the cell reference: INDIRECT(A1&"1")
Then find the address of the next column over: ADDRESS(1,COLUMN(INDIRECT(A10&"1"))+1)

Then from the $??$1 we extract the letters: 2 ways:

  1. Look for the second $, and snip the text out between them

    =MID(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),2,FIND("$",ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),2)-2)
    
  2. Replace the 1 and $ with nothing in the string

    =SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),"$",""),"1","")
    

Choose which one works best for you

| improve this answer | |
  • 2
    You can avoid the "$" substituting by including the optional third argument 4 in the ADDRESS function to make it return a relative cell reference. Then you just need the formula: =SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1,4),"1","") – Excellll Jan 23 '15 at 15:50
2

Another example: Type this into cell A1, then copy the formula to any or all cells.

=CHAR(MOD(ROW(A1)-1;26)+65)&CHAR(MOD(COLUMN(A1)-1;26)+65)

Intended as an example of how one may think about the problem.

| improve this answer | |
2

This will reset to "A" when it reaches "Z"

=IF(A1="Z", "A", CHAR(CODE(A1)+1))

You can build out beyond that with more if statements.

OR

I just wrote this formula for something similar:

IF(RIGHT(C2,1)="Z",IF(RIGHT(C2,2)="ZZ","A",CHAR(CODE(MID(C2,5,1))+1)),MID(C2,5,1))&IF(RIGHT(C2,1)="Z","A",CHAR(CODE(MID(C2,6,1))+1))
| improve this answer | |
1

As another solution for it with using ADDRESS() is:

=MID(ADDRESS(1,26+ROW()),2,2)

Above formula will return AA in first row and AB in second row and so on.

Also with using math the formula is:

=CONCATENATE(CHAR(INT(ROW()/26)+65),CHAR(MOD(ROW()-1,26)+65))
| improve this answer | |
1

I used this code to obtain Cell address

=ADDRESS(ROW($AT$17),COLUMN($AT$17)+Increment,4,1)

This example is for AT17 cell address.

Increment you define how many numbers you will increment

I wrote this code in cell Ax17.

Later I obtained value of AT17 with

=INDIRECT(AX17)

Done !!!

Now you can increment columns instead of rows !!!

| improve this answer | |
0

Here is my solution (cell A1 contains "AA"):

=IF(CHAR(CODE(RIGHT(A2;1))+1)="[";CHAR(CODE(LEFT(A2;1))+1);CHAR(CODE(LEFT(A2;1))))&IF(CHAR(CODE(RIGHT(A2;1))+1)="[";CHAR(65);CHAR(CODE(RIGHT(A2;1))+1))
| improve this answer | |
0

Fill Column A (from row 1) with consecutive numbers starting with 0 to 100 [or till requirement]

Fill Cell B1 with below formula

=CONCATENATE(CHAR(MOD(QUOTIENT(A1,26*26),26)+65),CHAR(MOD(QUOTIENT(A1,26),26)+65),CHAR(MOD(A1,26)+65))

Copy down the formula from B1 to other rows in Column B [till the row you have filled Column A]

This works for 3 characters AAA to ZZZ

Formula needs to be modified as per no. of characters required (AA to ZZ / AAAAA to ZZZZZ / etc)

| improve this answer | |
0

I know this is slightly off the main question, but I think it answers the fuller question... If you have a letter in A1, and you wish it to be stepped by a number in B1, the following formula combo will achieve it from single letters to ZZ.

 =IF(LEN(A1)>1,IF((CODE(RIGHT(A1,1))+$B$1)>CODE("Z"),CHAR(CODE(LEFT(A1,1))+1)&CHAR(CODE(RIGHT(A1,1))-21),LEFT(A1,1)&CHAR(CODE(RIGHT(A1,1))+$B$1)),IF((CODE(A1)+$B$1)>CODE("Z"),"A"&CHAR(CODE(A1)-21),CHAR(CODE(A1)+$B$1)))

Copy it down the column and the results are there. Change the B5 number and the results change.

| improve this answer | |
-1
=IF(AND(LEN(G1)=1,G1="Z"), "AA", IF(LEN(G1)=1, CHAR(CODE(G1)+1), IF(RIGHT(G1,1)<>"Z", LEFT(G1,1)&CHAR(CODE(RIGHT(G1,1))+1), CHAR(CODE(G1)+1)&"A")))
| improve this answer | |
  • 2
    Can you please add some explanations, extend your answer – Romeo Ninov Feb 19 '16 at 8:14
-1

For Columns, the below is the right solution.

=IF(LEN(ADDRESS(1,COLUMN()))=4,MID(ADDRESS(1,COLUMN()),2,1),MID(ADDRESS(1,COLUMN()),2,2))

| improve this answer | |
  • 3
    I don't see how your formula answers the question "How to increment string like AA to AB?". – Máté Juhász May 24 '18 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.