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

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

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