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


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.



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:

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



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