Excel - Convert column letter to number using COLUMN()

Every day, I find some new things or have new idea in the Excel. I suddenly figure it out a simple way to convert column letter to numbers. By modifying it, I can also quickly determine the column index number used in the VLOOKUP() function.

It is COLUMN() function.

For example,

What is the column number for column AB?

In cell A1, type in the formula =COLUMN(AB1).

The result is 28.

What is the corresponding column numbers for column BA to column BE?

In cell A1, type in the formula =COLUMN(BA1). Then drag the formula from A1 to E1.

The result is 53,54,55,56,57.

What is the column index number assuming a table array is from column G to column BA and the target column is Column AY?

In cell A1, type in the formula =COLUMN(AY1)-COLUMN(G1)+1.

The result is 45.

   

                                                                                © Copyright Exceltipsandkeys All Rights Reserved.