Excel formula: Convert column letter to number

Generic formula

=COLUMN(INDIRECT(letter&"1"))

Explanation

To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions.

In the example shown, the formula in C5 is:

=COLUMN(INDIRECT(B5&"1"))

How this formula works

The first step is to construct a standard "A1" style reference using the column letter, by adding a "1" with concatenation:

B5&"1"

This results in a text string like "A1" which is passed into the INDIRECT function.

Next, the INDIRECT function transforms the text into a proper Excel reference and hands the result off to the COLUMN function.

Finally, the COLUMN function evaluates the reference and returns the column number for the reference.

Author

Dave Bruns

Related formulas

Convert column number to letter

To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. In the example shown, the formula in C5 is: = SUBSTITUTE ( ADDRESS ( 1 , B5 , 4 ), "1" , "...

Related functions

Excel COLUMN Function

The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the...

Excel INDIRECT Function

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.