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

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):

• =COLUMN(<range>) returns a number. Ex. =COLUMN(D1) returns 4 Mar 21, 2022 at 20:17

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).

• Great! I also added this solution to the sample sheet.
– Dan
Mar 22, 2022 at 12:06
• @EngineerToast Sadly CODE("") in Libre Calc returns 0 and not a #VALUE! error like in Excel/Sheets. So while this is arguably the simplest formula for Excel, it doesn't play well with everyone. Mar 22, 2022 at 12:49
• @EngineerToast Thank you, I added your solution but the result is not always right
– Dan
Mar 22, 2022 at 12:51
• @Mobus Alas! I have no experience with Libre and did not notice the tag. Thank you for pointing this out for future users. Mar 22, 2022 at 12:57
• @EngineerToast sadly your IFERROR method doesn't seem to work, as MID(A1,1,1) of a single letter gives a least significant value (i.e. * 26^0), but MID(A1,1,1) of three letters gives a most significant value (i.e. * 26^2). Maybe MID(A1, LEN(A1)+1-{1,2,3},1)? Mar 22, 2022 at 13:37

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})
• I appreciate your answer, Mobus, but I couldn't get the MMULT formula working. Maybe you could take a look at the sample sheet with the solutions I posted and give me a tip what to change in the formula.
– Dan
Mar 22, 2022 at 7:19
• Both the last two formulae provide the same answer. The MMULT one is simply a shorter array formula implementation. If you don't have Excel 365 (which autodetects array formula/has dynamic arrays with spilling) you must confirm the formula with CTRL+SHIFT+ENTER (CSE) to tell Excel it is an array formula. Mar 22, 2022 at 8:45
• Ah actually the reason it didn't work is because you left out the intermediate step which "zero-pads" 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 column# 0. Mar 22, 2022 at 8:52
• In Google Sheets: =ARRAY_CONSTRAIN(ARRAYFORMULA(MMULT((CODE(MID(RIGHT(REPT(CHAR(CODE("A")-1),3)&A2, 3),{1,2,3},1))-CODE("A")+1),{676;26;1})), 1, 1)
– Dan
Mar 22, 2022 at 10:06
• Brilliant! Thank you so much for your efforts, Mobus!
– Dan
Mar 22, 2022 at 10:07

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).

• Thanks Ron! This works perfect for Excel and Google Sheets.
– Dan
Mar 22, 2022 at 7:20
• This is pretty much the same algorithm as mine, accept Ron does not zero pad to three characters. He basically (and cleverly) makes his array only as long as the source text and multiplies with the same sized {26^n, 26^(n-1), ... 26^0} vector. In this case SUMPRODUCT and MMULT is both doing the same dot product multiplication. Mar 22, 2022 at 9:22

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

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.

• Or, without a helper column (and untested in anything other than Excel): =MATCH(A1&"\$1",ADDRESS(1,SEQUENCE(16384),2),0) Mar 22, 2022 at 14:44
• Thank you, I added it to the solutions too. It works in Excel, and LibreCalc, but not in Google Sheets when columns are deleted.
– Dan
Mar 22, 2022 at 15:57
• It works just fine in Google Sheets so long as the input column exists. Unlike Excel and Calc, Sheets does not mandate how many columns there are. It starts with A-Z and you can add / delete as you like. Excel and Calc will always have their max (XFD & AMJ, respectively) even when you delete columns. Similarly, a solution that expects a max of 3 letters in the column address will fail if you want to calculate arbitrarily high columns like ABCD even though they don't exist in the sheet. Mar 22, 2022 at 16:54