Convert Numbers to Excel Column Names

This algorithm converts column numbers (1-x) to Excel column names. It works for any number and does not have a limit.

'--------------------------------------------------' Columns can be any number from 1 to x' ASCII A = 65 so since we're starting with 1' chr(intCol + 64 ) = A'--------------------------------------------------Function ExcelCol(ByVal intCol As Long) As String    'Subtract 1 for because intCol = 1 = 'A'    If intCol <= 26 Then        ExcelCol = Chr(intCol + 64)    Else        intRemainder = intCol Mod 26        intMod = intCol  26                If intRemainder = 0 Then            ExcelCol = ExcelCol(intMod - 1) & "Z"        Else            ExcelCol = ExcelCol(intMod) & Chr(intRemainder + 64)        End If    End IfEnd Function**** NON Recursive Version ***Function ExcelColNonRec(ByVal intCol As Long) As String    While intCol > 26        intRemainder = intCol Mod 26        intCol = intCol  26                If intRemainder = 0 Then            ExcelColNonRec = ExcelColNonRec & "Z"            intCol = intCol - 1        Else            ExcelColNonRec = ExcelColNonRec & Chr(intRemainder + 64)        End If    Wend    Col2 = Col2 + Chr(intCol + 64)End Function
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: