Lookup & Reference
Convert column number to letter (and back) with ADDRESS and COLUMN
Translate 1-based column numbers like 1, 26, 27, and 53 into A, Z, AA, and BA using ADDRESS and SUBSTITUTE—and reverse the conversion with COLUMN.
Why CHAR(64 + n) fails past column Z
A common shortcut, =CHAR(64 + n), works for columns 1 through 26 (A through Z) and then breaks at column 27, returning "[" (ASCII 91) instead of "AA".
Asking ADDRESS(1, col_num, 4) for row 1 in relative mode produces "A1", "Z1", "AA1", or "XFD1" for any valid spreadsheet column, and SUBSTITUTE(..., "1", "") strips the trailing row digit.
| Column Number | ADDRESS(1, n, 4) | Column Letter |
|---|---|---|
| 1 | A1 | A |
| 26 | Z1 | Z |
| 27 | AA1 | AA |
| 53 | BA1 | BA |
Column number to letter formula
Because relative row 1 contains only the digit "1" and column letters contain only A-Z, removing "1" leaves the exact multi-letter column label.
=SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")Reverse conversion and invalid column numbers
To convert a column letter stored in B2 back into its 1-based integer, append row "1" inside INDIRECT and pass the reference to COLUMN: =COLUMN(INDIRECT(B2 & "1")). Column numbers below 1 return #VALUE!.
=COLUMN(INDIRECT(B2 & "1"))Try the example
Use the original values or change them to test your understanding.