Convert column letter to number in Excel

by

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. See example below:

Formula

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

Explanation

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.

Worked Example:   Example of COUNTIFS with variable table column in Excel

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

Worked Example:   How to create dynamic worksheet reference in Excel

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

Worked Example:   How to abbreviate names or words in Excel

Leave a Reply

Your email address will not be published.