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

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

Answers

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.

By : PatrickDaemen

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

- For the 28th column - taking
`columnNumber=28`

;`Cells(1, columnNumber).Address`

returns`"$AB$1"`

. - Doing a split on the
`$`

sign returns the array:`["","AB","1"]`

- So
`Split(Cells(1, columnNumber).Address, "$")(1)`

gives you the column name`"AB"`

.

**UPDATE:**

Taken from How to convert Excel column numbers into alphabetical characters

```
' 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

By : Kent Pawar

In VBA, assuming lCol is the column number:

```
function ColNum2Letter(lCol as long) as string
ColNum2Letter = Split(Cells(1, lCol).Address, "$")(0)
end function
```

By : iDevlop

This video can help you solving your question :)

By: admin