google-sheetsrangecharactersubstitutionexcel-indirect

Generating the next column letter via formula


I am trying to grab a Column letter that is written in a cell and then generate what would be the next column's letter. This works for A to Z, but after that, my formula doesn't work

Let's say Cell A1 contains letter A and I want my function to show the next possible column, here B. This works:

=char(CODE(A1)+1)

However, if A1 contains letter Z, I want it to show the next column combination AA. Of if it's AZ, I'd like it to show BA. Is there any formula that can be created to execute this function?


Solution

  •   =SUBSTITUTE(ADDRESS(1, COLUMN(INDIRECT(A2&1))+1, 4), 1, )
    

    3