How to increment string like AA to AB?

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

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:

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)

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

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.

Related