# 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)
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