Column Letter ↔ Number
Free Column Letter ↔ Number: convert a column number to its letter with =SUBSTITUTE(ADDRESS(1,A2,4),"1","") and a letter to its number with...
Excel columns run A–XFD (1–16384).
A = 1 · XFD = 16384
How it works
In Excel or Google Sheets, convert a column number to its letter with =SUBSTITUTE(ADDRESS(1,A2,4),"1","") and a letter to its number with =COLUMN(INDIRECT(B2&"1")) — or use the instant converter above.
Number → letter: =SUBSTITUTE(ADDRESS(1,A2,4),"1","") where A2 holds the column number.
Letter → number: =COLUMN(INDIRECT(B2&"1")) where B2 holds the letters.
For one-off lookups, type the value into the converter above.
In VBA, use Cells(1, 28).Address or Range("AB1").Column instead of building strings.
What this does
Excel addresses columns two ways: letters (A, B, …, Z, AA, …, XFD) in the interface, and numbers (1 to 16384) in functions like COLUMN, ADDRESS, OFFSET, and in VBA. The letters are bijective base-26 — after Z comes AA, after AZ comes BA — which is why "what number is column AB" is not obvious mental math (it is 28).
A worked example
You need the letter for column 28 to build a range in a macro. =SUBSTITUTE(ADDRESS(1,28,4),"1","") returns AB: ADDRESS(1,28,4) builds the relative reference AB1, and SUBSTITUTE strips the row number. In the other direction, =COLUMN(INDIRECT("AB1")) returns 28. Macro authors, formula builders, and anyone wiring OFFSET/INDEX ranges constantly translate between the letter the sheet shows and the number the function needs.
Common mistakes
- Assuming AA is 27 because "A is 1, so AA is 26+1" — correct, but AZ→BA trips people up; it is base-26 with no zero digit.
- Using ADDRESS without the 4 (abs_num) argument and getting
$AB$1with dollar signs. INDIRECT-based letter→number formulas breaking when the cell contains a trailing space.- Hard-coding column letters in VBA that shift when columns are inserted — use named ranges or Cells(row, col).
FAQ
What is the last column in Excel?
XFD, which is column number 16384 (since Excel 2007).
What column number is AA?
AA is column 27. AB is 28, AZ is 52, BA is 53.
Is there a single built-in function for this?
No single function returns just the letter; the standard idiom combines ADDRESS with SUBSTITUTE as shown above.