11

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.

4
  • 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.
    – Andy K
    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
  • 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, 2015 at 12:24

13 Answers 13

12

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

1
  • 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, 2016 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

1
  • 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, 2015 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.

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

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

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.

0
0

This 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...

0

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

1
  • 1
    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
-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")))
1
  • 2
    Can you please add some explanations, extend your answer Feb 19, 2016 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))

1
  • 3
    I don't see how your formula answers the question "How to increment string like AA to AB?". May 24, 2018 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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