How to increment string like AA to AB?











up vote
9
down vote

favorite
4












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.










share|improve this question
























  • 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















up vote
9
down vote

favorite
4












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.










share|improve this question
























  • 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













up vote
9
down vote

favorite
4









up vote
9
down vote

favorite
4






4





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.










share|improve this question















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.







microsoft-excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 23 '15 at 15:56









Excellll

11k74162




11k74162










asked Jan 22 '15 at 10:40









Andy K

1901218




1901218












  • 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


















  • 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
















Does not work after Z? Do you mean after AZ or ZZ
– Dave
Jan 22 '15 at 11:10




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




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




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




Hi Dirk, many thanks for the advice. However why?
– Andy K
Jan 22 '15 at 12:14




1




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




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






active

oldest

votes

















up vote
10
down vote



accepted










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






share|improve this answer























  • =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


















up vote
3
down vote













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






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




















up vote
2
down vote













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.






share|improve this answer






























    up vote
    2
    down vote













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





    share|improve this answer






























      up vote
      1
      down vote













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





      share|improve this answer






























        up vote
        1
        down vote













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






        share|improve this answer






























          up vote
          0
          down vote













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





          share|improve this answer




























            up vote
            0
            down vote













            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)






            share|improve this answer




























              up vote
              0
              down vote













              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.






              share|improve this answer






























                up vote
                -1
                down vote













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





                share|improve this answer



















                • 2




                  Can you please add some explanations, extend your answer
                  – Romeo Ninov
                  Feb 19 '16 at 8:14


















                up vote
                -1
                down vote













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






                share|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 at 10:11











                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "3"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f867965%2fhow-to-increment-string-like-aa-to-ab%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                11 Answers
                11






                active

                oldest

                votes








                11 Answers
                11






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes








                up vote
                10
                down vote



                accepted










                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






                share|improve this answer























                • =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















                up vote
                10
                down vote



                accepted










                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






                share|improve this answer























                • =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













                up vote
                10
                down vote



                accepted







                up vote
                10
                down vote



                accepted






                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






                share|improve this answer














                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







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited May 27 '16 at 7:57

























                answered Jan 22 '15 at 11:00









                CallumDA

                962718




                962718












                • =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


















                • =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
















                =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




                =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












                up vote
                3
                down vote













                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






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

















                up vote
                3
                down vote













                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






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















                up vote
                3
                down vote










                up vote
                3
                down vote









                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






                share|improve this answer












                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







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 23 '15 at 15:43









                SeanC

                3,27411425




                3,27411425








                • 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




                  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




                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






                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












                up vote
                2
                down vote













                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.






                share|improve this answer



























                  up vote
                  2
                  down vote













                  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.






                  share|improve this answer

























                    up vote
                    2
                    down vote










                    up vote
                    2
                    down vote









                    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.






                    share|improve this answer














                    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.







                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited Jan 23 '15 at 20:58

























                    answered Jan 23 '15 at 20:42









                    Hannu

                    3,9421925




                    3,9421925






















                        up vote
                        2
                        down vote













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





                        share|improve this answer



























                          up vote
                          2
                          down vote













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





                          share|improve this answer

























                            up vote
                            2
                            down vote










                            up vote
                            2
                            down vote









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





                            share|improve this answer














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






                            share|improve this answer














                            share|improve this answer



                            share|improve this answer








                            edited Feb 8 '16 at 7:43









                            Andy K

                            1901218




                            1901218










                            answered Feb 8 '16 at 2:23









                            Nick Fleetwood

                            195




                            195






















                                up vote
                                1
                                down vote













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





                                share|improve this answer



























                                  up vote
                                  1
                                  down vote













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





                                  share|improve this answer

























                                    up vote
                                    1
                                    down vote










                                    up vote
                                    1
                                    down vote









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





                                    share|improve this answer














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






                                    share|improve this answer














                                    share|improve this answer



                                    share|improve this answer








                                    edited Feb 8 '16 at 9:27

























                                    answered Feb 8 '16 at 9:22









                                    shA.t

                                    372214




                                    372214






















                                        up vote
                                        1
                                        down vote













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






                                        share|improve this answer



























                                          up vote
                                          1
                                          down vote













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






                                          share|improve this answer

























                                            up vote
                                            1
                                            down vote










                                            up vote
                                            1
                                            down vote









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






                                            share|improve this answer














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







                                            share|improve this answer














                                            share|improve this answer



                                            share|improve this answer








                                            edited Jun 12 '16 at 5:32









                                            fixer1234

                                            17.6k144381




                                            17.6k144381










                                            answered Jun 11 '16 at 22:57









                                            Mark Twain

                                            113




                                            113






















                                                up vote
                                                0
                                                down vote













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





                                                share|improve this answer

























                                                  up vote
                                                  0
                                                  down vote













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





                                                  share|improve this answer























                                                    up vote
                                                    0
                                                    down vote










                                                    up vote
                                                    0
                                                    down vote









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





                                                    share|improve this answer












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






                                                    share|improve this answer












                                                    share|improve this answer



                                                    share|improve this answer










                                                    answered Jul 25 '17 at 7:11









                                                    Ivanoff

                                                    1




                                                    1






















                                                        up vote
                                                        0
                                                        down vote













                                                        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)






                                                        share|improve this answer

























                                                          up vote
                                                          0
                                                          down vote













                                                          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)






                                                          share|improve this answer























                                                            up vote
                                                            0
                                                            down vote










                                                            up vote
                                                            0
                                                            down vote









                                                            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)






                                                            share|improve this answer












                                                            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)







                                                            share|improve this answer












                                                            share|improve this answer



                                                            share|improve this answer










                                                            answered May 31 at 14:34









                                                            Sundar

                                                            1




                                                            1






















                                                                up vote
                                                                0
                                                                down vote













                                                                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.






                                                                share|improve this answer



























                                                                  up vote
                                                                  0
                                                                  down vote













                                                                  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.






                                                                  share|improve this answer

























                                                                    up vote
                                                                    0
                                                                    down vote










                                                                    up vote
                                                                    0
                                                                    down vote









                                                                    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.






                                                                    share|improve this answer














                                                                    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.







                                                                    share|improve this answer














                                                                    share|improve this answer



                                                                    share|improve this answer








                                                                    edited Nov 25 at 17:47









                                                                    Pierre.Vriens

                                                                    1,20561218




                                                                    1,20561218










                                                                    answered Nov 25 at 15:03









                                                                    Graham Porter

                                                                    1




                                                                    1






















                                                                        up vote
                                                                        -1
                                                                        down vote













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





                                                                        share|improve this answer



















                                                                        • 2




                                                                          Can you please add some explanations, extend your answer
                                                                          – Romeo Ninov
                                                                          Feb 19 '16 at 8:14















                                                                        up vote
                                                                        -1
                                                                        down vote













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





                                                                        share|improve this answer



















                                                                        • 2




                                                                          Can you please add some explanations, extend your answer
                                                                          – Romeo Ninov
                                                                          Feb 19 '16 at 8:14













                                                                        up vote
                                                                        -1
                                                                        down vote










                                                                        up vote
                                                                        -1
                                                                        down vote









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





                                                                        share|improve this answer














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






                                                                        share|improve this answer














                                                                        share|improve this answer



                                                                        share|improve this answer








                                                                        edited Feb 19 '16 at 1:09









                                                                        user380375

                                                                        1




                                                                        1










                                                                        answered Feb 18 '16 at 22:54









                                                                        user560874

                                                                        1




                                                                        1








                                                                        • 2




                                                                          Can you please add some explanations, extend your answer
                                                                          – Romeo Ninov
                                                                          Feb 19 '16 at 8:14














                                                                        • 2




                                                                          Can you please add some explanations, extend your answer
                                                                          – Romeo Ninov
                                                                          Feb 19 '16 at 8:14








                                                                        2




                                                                        2




                                                                        Can you please add some explanations, extend your answer
                                                                        – Romeo Ninov
                                                                        Feb 19 '16 at 8:14




                                                                        Can you please add some explanations, extend your answer
                                                                        – Romeo Ninov
                                                                        Feb 19 '16 at 8:14










                                                                        up vote
                                                                        -1
                                                                        down vote













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






                                                                        share|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 at 10:11















                                                                        up vote
                                                                        -1
                                                                        down vote













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






                                                                        share|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 at 10:11













                                                                        up vote
                                                                        -1
                                                                        down vote










                                                                        up vote
                                                                        -1
                                                                        down vote









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






                                                                        share|improve this answer












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







                                                                        share|improve this answer












                                                                        share|improve this answer



                                                                        share|improve this answer










                                                                        answered May 24 at 10:08









                                                                        user2459372

                                                                        1




                                                                        1








                                                                        • 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 at 10:11














                                                                        • 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 at 10:11








                                                                        3




                                                                        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 at 10:11




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


















                                                                        draft saved

                                                                        draft discarded




















































                                                                        Thanks for contributing an answer to Super User!


                                                                        • Please be sure to answer the question. Provide details and share your research!

                                                                        But avoid



                                                                        • Asking for help, clarification, or responding to other answers.

                                                                        • Making statements based on opinion; back them up with references or personal experience.


                                                                        To learn more, see our tips on writing great answers.





                                                                        Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                                                                        Please pay close attention to the following guidance:


                                                                        • Please be sure to answer the question. Provide details and share your research!

                                                                        But avoid



                                                                        • Asking for help, clarification, or responding to other answers.

                                                                        • Making statements based on opinion; back them up with references or personal experience.


                                                                        To learn more, see our tips on writing great answers.




                                                                        draft saved


                                                                        draft discarded














                                                                        StackExchange.ready(
                                                                        function () {
                                                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f867965%2fhow-to-increment-string-like-aa-to-ab%23new-answer', 'question_page');
                                                                        }
                                                                        );

                                                                        Post as a guest















                                                                        Required, but never shown





















































                                                                        Required, but never shown














                                                                        Required, but never shown












                                                                        Required, but never shown







                                                                        Required, but never shown

































                                                                        Required, but never shown














                                                                        Required, but never shown












                                                                        Required, but never shown







                                                                        Required, but never shown







                                                                        Popular posts from this blog

                                                                        Plaza Victoria

                                                                        Puebla de Zaragoza

                                                                        Musa