This tutorial demonstrates how to convert a column letter to a number using the COLUMN and INDIRECT function in Excel.

 

Convert Column Letter to Number Main Function

COLUMN Function

The COLUMN function returns the column number of a cell reference. Its syntax is:

=COLUMN(Cell Reference)

To illustrate, let’s see some examples below.

=COLUMN(A1) has a result of 1.  This is because the cell reference “A1” is in column number 1.

=COLUMN(B1) has a result of 2.  This is because the cell reference “B1” is in column number 2.

=COLUMN(H1) has a result of 8.  This is because the cell reference “H1” is in column number 8.

REF 02 EX 01

REF 02 EX 02

INDIRECT Function

The INDIRECT function converts a text string into a cell reference. It’s syntax is:

=INDIRECT(Text String of Cell Reference)

To illustrate, let’s see an example below.

=INDIRECT("A1")

Indirect

 

In the above example, cell A1 has the value 500.  By inputting INDIRECT(“A1”), you are indirectly referencing cell A1 (hence the name).

Converting Column Letter to Number

Using a combination of the above two functions, we can find the column number of a valid column letter.  By concatenating a column letter and 1, it gives us a cell reference. Since INDIRECT converts a text string into a cell reference, we can use it with COLUMN to get the column letter.

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

Column and Indirect

Convert Column Letter to Number in Google Sheets

The combination of COLUMN and INDIRECT Functions works exactly the same in Google Sheets as in Excel:

Convert Column Letter to Number Go