User Rating: 4 / 5

Excel VBA - Convert Excel Column Number to Column Letter, and Column Letter to Column Number

-------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------------------------

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

'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

-------------------------------------------------------------------------------------------------------------------------

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