Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB
Expertise: Intermediate
Mar 29, 2006

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 If
End 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
Yassine Moe
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap