How to CONVERT COLUMN TO NUMBER FORMULA in Excel

This formula is used to find out the number of any column letter (e.g. A, B, C, D, etc). This can have many purposes. For instance, in the OFFSET formula, we make reference to a specific cell, and then the lookup to that cell can be offset by the number of rows or columns from it. If you use the COLUMN formula, you can count how many columns away a particular cell is.

Formula explanation:

There are two ways in which we can use the COLUMN formula.

1) = COLUMN(REFERENCE CELL)
This formula will return the column number of the reference cell.

2) =COLUMN(INDIRECT(ref_text, [a1]))
This formula will allow you to enter a column letter in the ref_text part of the formula to return the column number of that column letter.

• COLUMN: To get the column number.
• INDIRECT: To find the ref_text.
• ref_text: Cell containing the column letter for which we are finding it’s number.
• a1: Logical value that helps to specify the reference.

Example #1:

Let’s say we have a number in cell I23 of 100 and want to make reference to its column number. 100

Solution #1:

We could use the above formula option #1 to determine this as follows:

Column: 9 =COLUMN(I23)

Example #2:

Let’s say we have a table full of column letters and we want to make a list of their column numbers. We can use the COLUMN formula with the nested INDIRECT formula to determine this.

Solution #2:

In order to find out the required result, we have the complete following procedure:

1) Go to the cell where you want to find out the result.

2) Write the formula according to above syntax =COLUMN(INDIRECT(ref_text, [a1]))

3) For the ref_text, make it equal to the column letter and type & “1”

This is a great shortcut if you want to calculate the number of columns if you are making reference to column numbers or the location of certain data within a financial model this would come in handy.

Keep in mind, that we consistently use the logical value of 1 within the indirect formula and it will produce the intended results.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!