Excel Letter to Number: Convert Column Labels in Formulas

To convert an Excel column letter to its number, place the label in A2 and enter =COLUMN(INDIRECT(UPPER(A2)&"1")) in B2. For example, if A2 contains AA, the result is 27. Excel numbers columns A=1 through Z=26, then continues AA=27 and onward.

What Excel column letters mean

Excel uses letters to name worksheet columns and numbers to name rows. A cell address combines both: B3 means the cell in column B, row 3. The letters are not abbreviations for fixed numbers; they are labels in a sequence that continues after Z. This is why converting a label such as XFD takes more than assigning A=1, B=2, and so on.

The sequence works like a base-26 count with one important difference: it has no zero digit. A is 1, B is 2, and Z is 26. After Z, Excel starts the next place with A, so AA is 26+1, or 27. AB is 28, AZ is 52, and BA is 53. The lack of a zero column is why ordinary positional base-26 calculations need an adjustment if you build your own formula.

These numbers are useful when a task needs a numeric column index rather than a worksheet label. For example, a formula or script may need to calculate how far apart two columns are, or a report may need to display column positions as numbers. Excel itself still shows column letters at the top of the sheet in its usual A1 reference style.

Convert a letter with COLUMN and INDIRECT

The short formula uses COLUMN to return a reference’s column number and INDIRECT to turn text into a reference. With a valid column label in A2, enter =COLUMN(INDIRECT(UPPER(A2)&"1")) in B2. If A2 contains C, the formula builds the reference C1 and COLUMN returns 3. If A2 contains AZ, it builds AZ1 and returns 52.

The row number added to the label is simply there to make a complete cell reference. Since the formula asks for the column number, the chosen row does not change the result. UPPER makes lowercase input such as az work as well. Excel references are not case-sensitive, but using UPPER makes the intended treatment explicit.

INDIRECT is convenient, but it is a volatile function: Excel recalculates it whenever the workbook recalculates, even if the text in A2 has not changed. In a small sheet this is usually inconsequential. In a very large workbook full of INDIRECT formulas, recalculation can become slower. The next approach avoids constructing a worksheet reference.

Use a non-volatile formula for letter labels

In Microsoft 365 or Excel 2021 and later, a formula can interpret each letter as a base-26 digit. With a valid label of one or more English letters in A2, enter =SUMPRODUCT((CODE(MID(UPPER(A2),SEQUENCE(LEN(A2)),1))-64)*26^(LEN(A2)-SEQUENCE(LEN(A2)))) in B2. For AZ, it calculates 1×26+26 and returns 52; for XFD, it returns 16,384.

Here, MID extracts each character, CODE converts its uppercase letter to a character code, and subtracting 64 maps A to 1 through Z to 26. SEQUENCE supplies the character positions, and the powers of 26 give earlier letters their place values. SUMPRODUCT adds the resulting values. This formula is non-volatile, but it is longer and assumes the input contains only letters A through Z. It is not a validation formula for arbitrary text.

For Excel versions without SEQUENCE, the COLUMN and INDIRECT formula is generally the simpler choice. If the workbook needs to accept untrusted or messy input, validate the text before converting it rather than assuming every entry is a legal column label.

Convert a column number back to letters

To reverse the conversion, put the column number in A2 and use =SUBSTITUTE(ADDRESS(1,A2,4),"1","") in B2. With 27 in A2, ADDRESS creates the relative cell address AA1, and SUBSTITUTE removes the row number, leaving AA. With 16,384, the result is XFD, Excel’s last worksheet column.

ADDRESS takes a row number and a column number; the third argument, 4, requests a relative address with neither a dollar sign before the column nor one before the row. The formula uses row 1 only to create a valid address. This conversion is limited to valid worksheet column numbers, from 1 to 16,384. A zero, negative number, or number beyond the worksheet’s last column is not a valid column index.

Convert a whole list of labels

For a list of labels, put each value in its own row in column A, beginning at A2. Enter the COLUMN/INDIRECT formula in B2, then copy it down using the fill handle at the lower-right corner of the selected cell. Excel adjusts A2 to A3, A4, and so on. You can also select B2 and drag the fill handle down to the last row that needs a result.

For example, if A2:A5 contains A, Z, AA, and XFD, the corresponding results in B2:B5 are 1, 26, 27, and 16,384. Check a few entries against these boundaries before relying on a large conversion. If you need to keep the results but remove the formulas, copy column B and use Paste Special > Values to replace formulas with their current results.

The reverse conversion works the same way: put numbers in A2 downward, enter the ADDRESS/SUBSTITUTE formula in B2, and fill it down. Keep the input and output in separate columns while checking the results; writing output over the source list can overwrite values that later formulas still need.

Common errors and input limits

A #REF! result from the COLUMN/INDIRECT method usually means the text did not form a valid cell reference. Examples include an empty cell, punctuation, spaces inside a label, or letters beyond XFD. Excel columns end at XFD, which is number 16,384; a label that would denote a later column cannot identify a worksheet column.

Leading or trailing spaces are another frequent cause. If A2 may contain accidental spaces, use =COLUMN(INDIRECT(UPPER(TRIM(A2))&"1")) instead. TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces to one, though an internal space still makes the resulting reference invalid. Some imported data contains nonbreaking spaces, which TRIM does not remove; those may need to be cleaned separately.

Be clear about what the input represents. The formulas above convert a column label such as BC, not a full cell address such as BC12, a range such as BC:BD, or a label prefixed with a dollar sign such as $BC. If your source contains full addresses, first isolate the column letters or use a method designed for the exact address format. Do not assume that removing digits from arbitrary text will always produce a valid label.

Another common mistake is treating the label as a normal base-26 number with A=0. Excel starts at A=1 and has no zero letter, so a plain conversion gives the wrong answer after Z. For instance, AA is 27, not 26. The COLUMN method handles Excel’s numbering convention automatically; the longer formula accounts for it by mapping A to 1.

Column numbers, cell references, and compatibility

A column number is not the same as a cell’s row number or its position within a selected range. COLUMN(A1) returns 1 because A is the first worksheet column; COLUMN(D1) returns 4. By contrast, ROW(D1) returns 1. If a formula needs the position of a cell within a smaller range, that is a different calculation from the worksheet’s absolute column number.

The formulas here work in Excel for Microsoft 365 on Windows, and the core functions are also available in Excel for Mac and Excel for the web. The main compatibility distinction is the non-volatile formula: SEQUENCE is available in Microsoft 365 and Excel 2021 or later, while the INDIRECT version uses older, broadly supported functions. In Excel for the web, formulas recalculate in the workbook just as formulas do in the desktop app, although some workbook-management features differ.

If you are using a formula inside a larger calculation, remember that the result is numeric. For example, a column label converted to 27 can be compared with other numbers, used in arithmetic, or passed to a function that expects a column index. It does not change the worksheet’s visible headings or move data; it only returns a value in the cell containing the formula.

Choosing the right conversion method

Use COLUMN with INDIRECT when you want a short, easy-to-read formula and your input is a valid worksheet column label. It is usually the most practical answer for a few conversions or a compatibility-focused workbook. Use the SEQUENCE and SUMPRODUCT method when avoiding volatile functions matters and the workbook uses Microsoft 365 or Excel 2021 and later.

For a number-to-letter conversion, ADDRESS with SUBSTITUTE is direct and easy to audit. Whichever direction you need, keep a few known examples nearby—A=1, Z=26, AA=27, and XFD=16,384. These checks catch the most common off-by-one error and confirm that a formula is returning the worksheet’s column index rather than a different position.

Frequently Asked Questions

What number is Excel column AA?
AA is column 27. The first A contributes 26 and the second A contributes 1, giving 27.

What is the Excel formula to change letters to a column number?
If A2 contains a valid column label, enter =COLUMN(INDIRECT(UPPER(A2)&"1")). For example, AA returns 27.

How do I change a column number to a letter?
If A2 contains a valid column number, use =SUBSTITUTE(ADDRESS(1,A2,4),"1",""). The value 28 returns AB.

What is the number of Excel’s last column, XFD?
XFD is column 16,384. Excel worksheets have 16,384 columns, from A through XFD.

Can Excel convert lowercase letters such as aa?
Yes. Excel column references are case-insensitive, and the formula shown also applies UPPER to normalize lowercase input before building the reference.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.