This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel.
Excel column letter to column number / column number to column letter conversion
Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet.
For example, for Cell D1, column number is 4, while column letter is D.
Excel convert column number to column letter
To convert column number to column letter, make use of Address Function, which combines column number and row number and convert to a Cell address with column letter (such as A1).
Syntax of Excel Address Function
ADDRESS( row, column, [abs_num], [a1], [sheet_text] )
Example
Assume that you have a column number 4, you want to convert to letter D.
| Formula | Value | Explanation | |
| Step 1 | =ADDRESS(1,4,4) | D1 | Put a dummy row number 1 in address, return relative reference |
| Step 2 | =SUBSTITUTE(ADDRESS(1,4,4),1,””) | D | Replace 1 as nothing |
Excel convert column letter to column number
To convert column number to column letter, make use of Indirect Function, which turns a Text into Reference.
Assume that you have a column letter D, you want to convert to number 4.
=COLUMN(INDIRECT("D1"))
VBA custom Function – convert column letter to column number
This Function takes an argument ColNm (colNm means column name, e.g. A,B,C) and return column number.
VBA code
Public Function wColNum(ColNm) wColNum = Range(ColNm & 1).Column End Function
Explanation of VBA code
Take colNm = A as example
– use dummy row number 1 to create a Range(A1)
– return column number of Range(A1)
Example
| Formula | Result |
| =wColNum(“D”) | 4 |
| =wColNum(“AA”) | 27 |
VBA custom Function – convert column number to column letter
This Function takes an argument ColNum (colNum means column number, e.g. 1,2,3) and return column name (A,B,C)
VBA code
Public Function wColNm(ColNum) wColNm = Split(Cells(1, ColNum).Address, "$")(1) End Function
Explanation of VBA code
Take ColNum = 1 as example
– use dummy row number 1 to create a dummy Cells(1,1)
– return the address Cells(1,1), which is $A$1
– Split $A$1 by $, get the array item (1)
Example
| Formula | Result |
| =wColNm(1) | A |
| =wColNm(27) | AA |
Outbound References