Get the Column Letter from a Number

Excel Formulas › Lookup

All versionsADDRESSSUBSTITUTE

Need the column letter for column number 28 (it’s AB)? Excel has no direct function, but ADDRESS builds a cell reference you can strip down to just the letters — handy for dynamic references and reporting.

Quick formula: to turn the column number in A2 into its letter:
=SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")
ADDRESS makes a reference like AB1; removing the row number 1 leaves just AB.

Functions used (tap for the full reference guide):

ADDRESSSUBSTITUTECOLUMN

The example

Column numbers converted to their letters.

AB
1NumberLetter
21A
328AB
4100CV

The formula

The column letter for a number:

=SUBSTITUTE(ADDRESS(1, A2, 4), "1", "") // 28 → AB

How it works

Build an address, then keep only the letters:

  1. ADDRESS(1, A2, 4) builds a relative reference for row 1, column A2 — for 28 that’s AB1. The 4 means relative (no $ signs).
  2. SUBSTITUTE(…, "1", "") removes the row number, leaving just the column letters AB.
  3. Use row 1 so the only digit to remove is 1 — that’s why the formula targets "1".
  4. Going the other way (letter → number)? =COLUMN(AB1) returns 28.

Column letter of the current cell: =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") — COLUMN() feeds its own column number in, so the formula reports whichever column it sits in.

Try it: interactive demo

Live demo

Enter a column number; get its letter.

Letter:

Variations

Letter → number

COLUMN with a reference:

=COLUMN(AB1)

Current column letter

Self-referencing:

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

Full address as text

The whole reference (with row):

=ADDRESS(A3, A2)

Pitfalls & errors

Use row 1 in ADDRESS. The SUBSTITUTE removes the digit "1"; with any other row, removing its digits could mangle a letter (none contain digits, but the row 1 keeps it simple and safe).

Don’t confuse the 4th argument. 4 gives a plain relative reference; 1 would add $ signs that you’d then have to strip too.

Numbers beyond 16384 error. That’s the last column (XFD); ADDRESS can’t build a reference past it.

Practice workbook

📊
Download the free Get the Column Letter from a Number practice workbook
Numbers with the live column-letter formula, the letter-to-number and current-column variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get the column letter from a number in Excel?
Use =SUBSTITUTE(ADDRESS(1, A2, 4), "1", ""). ADDRESS builds a reference like AB1 and removing the row digit leaves the column letter.
How do I convert a column letter back to a number?
Use COLUMN with a reference in that column: =COLUMN(AB1) returns 28.
How do I get the letter of the current column?
Feed COLUMN() into the formula: =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") reports whichever column the formula is in.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Build references with INDIRECT · Get the current sheet name · 2-D INDEX/MATCH/MATCH

Function references: ADDRESS · SUBSTITUTE · COLUMN