How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example:

• Given column A, set to +1, it should return B
• Given column E, set to -2, it should return C
• Given column Y, set to +3, it should return AB
• Given column BAA, set to -1, it should return AZZ
• Given column C, set to * 2, it should return F

It should be simple to modify to be either relative to the column it's in, or take a hard-coded column letter, or get a column letter from a formula

Note that the popular formula =CHAR(CODE(A1)+1) doesn't work beyond Z.

Solution 1:

Here's the best I've found so far:

=SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")

The part in the middle marked in bold is the only part that changes.

In this example, it's taking the current column and adding 1, so returning B if it's in column A and AA if it's in column Z.

Example variants

Example adding to a fixed column (adding 2 to column Z, returns "AB"):

=SUBSTITUTE(ADDRESS(1,( COLUMN(Z1) + 2 ),4),1,"")

Example reading a cell A1 that contains a letter as text to be treated as a column letter:

=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,"")

Example reading a cell A1 that contains a number to be treated as a column number (e.g. if it contains 25, it'll return AB):

=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,"")

Looking up cells with indirect()

If your goal is to look up a cell, e.g. using indirect(), you can skip the substitute() and just pipe in the row number. For example, if you want to look up the cell to the right of the current cell (so increasing the cell letter by one and keeping the row the same):

=INDIRECT( ADDRESS( ROW(), ( COLUMN() + 1 ),4) )

Based partly on this which has a handy formula for transposing to get a column of column letters, incremented each row.

Solution 2:

The easiest way to do it is with an INDIRECT AND R1C1 reference style

for instance =INDIRECT("R[1]C[1]",0) will reference +1 row and +1 column from the current cell.

=INDIRECT("RC[3]",0) will go three columns to the right, where -3 goes three columns to the left.

You can use some concatenate or & to reference a cell for starting or increasing if you'd like:

A1 =INDIRECT("RC["&A2&"]",0)

A2 = 1

B1 = x

then A1 = x

Once you throw in some cell, row, column and address it gets more complicated, but the effect is the same.