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.
412 Answers
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
1We 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
1Another 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.
0This is the formula that should follow cell with letter A
=LEFT(ADDRESS(1,ROW(A2,4,1),(ROW(A2(>26)+1)
then distribute it across the column
It will work from A to DDDD...
=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")))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))
1
