Excel convert column letter to column number vise versa

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

https://support.office.com/en-us/article/address-function-47657e92-81ae-47f8-87cd-62d4f30c774d?ui=en-US&rs=en-US&ad=US&fromAR=1

 

Excel convert column letter to column number vise versa