Excel: How to convert a column letter to the corresponding column number without using COLUMN() and INDIRECT()?

Question

The standard solution seems to be =COLUMN(INDIRECT(letter&"1")) but there should be a better solution that avoids the volatile INDIRECT function.

Sample Column Letter	Corresponding Column Number
A	1
C	3
Z	26
AA	27
ZZ	702

There is a formula to convert a number to a column letter but not the other way round.

A solution which works with different spreadsheet software is preferred.

---

Suggested solutions compared (sample sheet):

• Google Sheets
• XLSX-Version for Excel
• ODS-Version for LibreOffice Calc

Answer 1

An alternative answer without zero-padding or Excel 365 formula like SEQUENCE - works in Excel, Libre Calc and Google Sheets

=SUMPRODUCT((CODE(MID(A1,CHOOSE(LEN(A1),{1},{1,2},{1,2,3}),1))-CODE("A")+1),CHOOSE(LEN(A1),{1},{26,1},{676,26,1}))

Note: This is an array formula. In version prior to Excel 365, you might have to confirm the formula with CTRL+SHIFT+ENTER (CSE) (although possibly not because SUMPRODUCT is array aware and produces a scalar value).

Answer 2

Basic solution for a single letter column e.g. A to Z

=CODE(A1)-CODE("A")+1

Where A1 contains a capital letter representing a column reference between A and Z.

Basic solution for multi-letter column e.g. A, AA or AAA

For multi-letter columns we need to be a bit smarter. First in B1 (helper column) we "zero" pad so we can handle one, two and three letter columms:

=RIGHT(REPT(CHAR(CODE("A")-1),3)&A1, 3)

This extends references like A and AA to three character references @@A and @A. The "@" character has character code 64 (one below "A") and is thus equivalent to a column# of 0.

Now we take each character of the three character reference, substract the letter offset for "A" and multiply with 26^2, 26^1 and 26^0 respectively to get the column number in C1:

=(CODE(LEFT(B1,1))-CODE("A")+1)*26*26 + (CODE(MID(B1,2,1))-CODE("A")+1)*26 + (CODE(RIGHT(B1,1))-CODE("A")+1)

In Excel 365, you can do away with the helper column (B) by using LET, or rather use the faster array formula below.

Fast array formula solution for multi-letter references

The array version puts the three characters in an array, substracts the "A" character code, then multiply with a column vector {26^2; 26^1; 26^0}

=MMULT((CODE(MID(B1,{1,2,3},1))-CODE("A")+1),{676;26;1})

which combined with the zero-pad formula gives

=MMULT((CODE(MID(RIGHT(REPT(CHAR(CODE("A")-1),3)&A1, 3),{1,2,3},1))-CODE("A")+1),{676;26;1})

Answer 3

If you have your column letter in A1 (just the letter, not a row), and if you have Office 365 with the SEQUENCE function, you can use:

=SUMPRODUCT(CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1))-64,26^SEQUENCE(LEN(A1),,LEN(A1)-1,-1))

You could, if you want, add code to return an error message if the calculation results in a column number less than 1 or greater than 16384 (depending on your version of Excel).

Answer 4

An entirely different approach: Create a list of all the addresses in row 1 and then find the address we want in that list.

=MATCH(A1&"1",ADDRESS(1,COLUMN($1:$1),4),0)

This works in Excel, Google Sheets, and LibreCalc. Concerning array formulas:

• Excel: Depending on version, you may need to enter this as an array formula using CTRL+SHIFT+ENTER.
• Google Sheets: You definitely need to enter this as an array formula. Alternatively, you can wrap the formula within the ArrayFormula() function manually which is the same thing that Sheets does when you press CTRL+SHIFT+ENTER.
• LibreCalc: You do not need to enter this as an array formula in version 7.2.6 which is all that I tested.

The screenshots below show Excel, Sheets, and Calc in that order.