  Advertiser Disclosure
 TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow     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 Submit a Tip Browse "XML" Tips Browse All Tips   Thanks for your registration, follow us on our social networks to keep up-to-date