# How to increment string like AA to AB?

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, 2015 at 11:10
• Hi Dave, I've tried AB and it has been looking for B since. Jan 22, 2015 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` Jan 22, 2015 at 12:11
• If you were planning to use the resulting strings in an `INDIRECT` formula for example, then @DirkHorsten's comment might be worth noting. Jan 22, 2015 at 12:24

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

• =IF(RIGHT(A1)="Z",CHAR(CODE(LEFT(A1))+1),LEFT(A1))&IF(RIGHT(A1)<>"Z",CHAR(CODE(RIGHT(A1))+1),CHAR(65)) Jan 14, 2016 at 16:01

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

• 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","")` Jan 23, 2015 at 15:50

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.

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

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

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 !!!

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

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)

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.

This is the formula that should follow cell with letter A

then distribute it across the column

It will work from A to DDDD...

For all the people looking at this in 2022. I’ve found this perfect response, it includes a downloadable Excel file. Finally found the answer I was looking for. All credits to the original author, hence the reason I didn’t copy and pretend this was my original response. Please check: https://techcommunity.microsoft.com/t5/excel/autofill-alphabet/m-p/2524584#M105901

• While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review Sep 16, 2022 at 16:28
``````=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")))
``````