Excel VBA – Convert Excel Column Number to Column Letter, and Column Letter to Column Number
Related Links: VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions.
——————————————————————————————-
Contents:
VBA Codes to Convert Excel Column Number to corresponding Column Letter
VBA Codes to Convert Excel Column Letter to corresponding Column Number
——————————————————————————————-
VBA Codes to Convert Excel Column Number to corresponding Column Letter
Sub ColumnNumberToColumnLetter1()
‘convert excel column number to corresponding column letter using vba Chr function, viz. column no. 52 will convert to AZ.
‘code supports maximum of 4 letter columns ie. upto column letters “ZZZZ” which equates to column number 475254.
‘Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).
Dim lngColNumber As Long
Dim strColLetter As String
‘enter column number for which you want the corresponding column letter
lngColNumber = 25000
If lngColNumber < 1 Then
MsgBox “Out of Range”
Exit Sub
‘for one letter column
ElseIf lngColNumber < 27 Then
‘first letter “A” has a column number of 65, hence the use of 64 (65 minus 1)
strColLetter = Chr(lngColNumber + 64)
‘for two letter column
ElseIf lngColNumber < 703 Then
‘first letter “A” has a column number of 65, and one round of alphabets “A” to “Z” equals 26
strColLetter = Chr(Int((lngColNumber – 1) / 26) + 64) & Chr(((lngColNumber – 1) Mod 26) + 65)
‘for three letter column
ElseIf lngColNumber < 18279 Then
‘676 equates to 26*26; 702 equates to 26*26+26;
strColLetter = Chr((Int((lngColNumber – 703) / 676) Mod 26) + 65) & Chr((Int((lngColNumber – 27) / 26) Mod 26) + 65) & Chr(Int((lngColNumber – 1) Mod 26) + 65)
‘for four letter column
ElseIf lngColNumber < 475255 Then
‘17576 equates to 26*26*26; 18278 equates to 26*26*26+702;
strColLetter = Chr((Int((lngColNumber – 18279) / 17576) Mod 26) + 65) & Chr((Int((lngColNumber – 703) / 676) Mod 26) + 65) & Chr((Int((lngColNumber – 27) / 26) Mod 26) + 65) & Chr(Int((lngColNumber – 1) Mod 26) + 65)
Else
MsgBox “Out of Range”
Exit Sub
End If
MsgBox strColLetter
End Sub
————————————————————————————————————————–
To download Excel file with live code, click here.
Sub ColumnNumberToColumnLetter2()
‘convert excel column number to corresponding column letter, viz. column no. 52 will convert to AZ
‘this code will work within the column limits of your current excel workbook viz. excel 2003 column number limit of 256 (ie. column letters IV), and excel 2007 column limit of 16384 (column letters XFD).
Dim lngColNumber As Long
Dim strColLetter As String
‘enter column number for which you want the corresponding column letter
lngColNumber = 256
‘if column number is outside your excel range, the code will give an error
On Error GoTo ErrorHandler
‘for column number 256 strColLetter returns “IV”, the first element of the array {IV;1} resulting from the Split function
‘”Cells(1, lngColNumber).Address” will return $IV$1, for column number 256
strColLetter = Split(Cells(1, lngColNumber).Address, “$”)(1)
‘returns the column letter
MsgBox strColLetter
‘exit sub after returning column letter, before executing the ErrorHandler
Exit Sub
‘error message if column number is outside your excel range
ErrorHandler:
MsgBox “Column Number ” & lngColNumber & ” is Outside your Excel Version Range”
End Sub
VBA Codes to Convert Excel Column Letter to corresponding Column Number
Sub ColumnLetterToColumnNumber1()
‘convert excel column letter to corresponding column number using vba Chr function, viz. column letter AZ will convert to 52
‘code supports maximum of 2 letter columns ie. upto column letters “ZZ” which equates to column number 702
‘Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).
Dim lngColNumber As Long
Dim strColLetter As String
‘enter column letter in lower or upper case – lowercase will get converted to uppercase
strColLetter = “zz”
strColLetter = UCase(strColLetter)
‘for one letter column
If Len(strColLetter) = 1 Then
lngColNumber = Asc(strColLetter) – 64
‘for two letter column
ElseIf Len(strColLetter) = 2 Then
lngColNumber = (Asc(Left(strColLetter, 1)) – 64) * 26 + (Asc(Right(strColLetter, 1)) – 64)
Else
MsgBox “Out of Range”
Exit Sub
End If
MsgBox lngColNumber
End Sub
———————————————————————————————————————–
Sub ColumnLetterToColumnNumber2()
‘convert excel column letter to corresponding column number using vba Chr function, viz. column letter AZ will convert to 52
‘code supports any number of column letters
‘Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).
Dim lngColNumber As Long, strColLetter As String, n As Integer
‘enter column letter in lower or upper case – lowercase will get converted to uppercase
strColLetter = “zz”
strColLetter = UCase(strColLetter)
lngColNumber = 0
For n = 1 To Len(strColLetter)
‘one round of alphabets “A” to “Z” equals 26; “lngColNumber * 26” will return zero for single column letter
lngColNumber = lngColNumber * 26 + (Asc(Mid(strColLetter, n, 1)) – 64)
Next
MsgBox lngColNumber
End Sub
————————————————————————————————————————-
To download Excel file with live code, click here.
Sub ColumnLetterToColumnNumber3()
‘convert excel column letter to corresponding column number
‘this code will work within the column limits of your current excel workbook viz. excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).
Dim lngColNumber As Long
Dim strColLetter As String
‘enter column letter in lower or upper case
strColLetter = “AA”
‘if column letter is outside your excel range, the code will give an error
On Error GoTo ErrorHandler
‘for column letter “AA”, column number 27 is returned
‘for column letter “AA”, “strColLetter & “:” & strColLetter” returns “AA:AA”
lngColNumber = Range(strColLetter & “:” & strColLetter).Column
MsgBox lngColNumber
Exit Sub
‘error message if column letter is outside your excel range
ErrorHandler:
MsgBox “Column Letter ” & strColLetter & ” is Outside your Excel Version Range”
End Sub