Exceljet

Quick, clean, and to the point

Convert column letter to number

Excel formula: Convert column letter to number
Generic formula 
=COLUMN(INDIRECT(letter&"1"))
Summary 

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"))
Explanation 

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 
Excel formula: Convert column number to letter
Convert column number to letter
The first step is to construct an address that contains the column number. We do this with the ADDRESS function, by providing 1 for row number, a column number from B5, and 4 for the abs_num argument (to get a relative reference): ADDRESS ( 1 , B5...
Related functions 
Excel COLUMN function
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
Excel INDIRECT Function

The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell 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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.