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

In this example, the goal is to convert an ordinary number into a column reference expressed in letters. For example, the number 1 should return "A", the number 2 should return "B", the number 26 should return "Z", etc. The challenge is that Excel...

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 cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid 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

Exceljet

Quick, clean, and to the point

Convert column letter to number

Excel Formula Training

Download 100+ Important Excel Functions

Excel video training