How do I find the Excel column name that corresponds to a given integer? [duplicate]

By : goric
Source: Stackoverflow.com
Question!

How would you determine the column name (e.g. "AQ" or "BH") of the nth column in Excel?

Edit: A language-agnostic algorithm to determine this is the main goal here.

By : goric

FROM wcm:

If you don't want to use VBA, you can use this replace colnr with the number you want

``````=MID(ADDRESS(1,colnr),2,LEN(ADDRESS(1,colnr))-3)
``````

Please be aware of the fact that this formula is volatile because of the usage of the ADDRESS function. Volatile functions are functions that are recalculated by excel after EVERY change. Normally excel recalculates formula's only when their dependent references changes.

It could be a performance killer, to use this formula.

This works fine in MS Excel 2003-2010. Should work for previous versions supporting the Cells(...).Address function:

1. For the 28th column - taking `columnNumber=28`; `Cells(1, columnNumber).Address` returns `"\$AB\$1"`.
2. Doing a split on the `\$` sign returns the array: `["","AB","1"]`
3. So `Split(Cells(1, columnNumber).Address, "\$")(1)` gives you the column name `"AB"`.

UPDATE:

``````' The following VBA function is just one way to convert column number
' values into their equivalent alphabetical characters:

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
``````

APPLIES TO: Microsoft Office Excel 2007 SE / 2002 SE / 2000 SE / 97 SE

In VBA, assuming lCol is the column number:

``````function ColNum2Letter(lCol as long) as string
end function
``````
By : iDevlop