Convert Column Number to Letter and Vice Versa in Excel

Sometimes when I built the application for Excel, I’ll need to convert the Column number to Letter (say, 3 is C) or vice versa.

I’ve been searching around, and finally found the following methods which are quite useful and simple. They cater for column letter like “AB” too.

Function: Convert column number to letter ( 2 –> “B”)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. 2 --> "B") 

   3: '   Parameters:     The column number (Long) 

   4: '   Return:         The column letter (String) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnNumberToLetter(ByVal ColumnNumber As Long) As String 

   7:  

   8:     Dim s0 As String, s1 As String, S2 As String, s3 As String 

   9:     If ColumnNumber > 18278 Then s0 = Chr((Int((ColumnNumber - 18279) / 17576) Mod 26) + 65) 

  10:     If ColumnNumber > 702 Then s1 = Chr((Int((ColumnNumber - 703) / 676) Mod 26) + 65) 

  11:     If ColumnNumber > 26 Then S2 = Chr(m_ConvertColumnNumberToLetter & (Int((ColumnNumber - 27) / 26)) Mod 26 + 65) 

  12:     

  13:     s3 = Chr(((ColumnNumber - 1) Mod 26) + 65) 

  14:     m_ConvertColumnNumberToLetter = s0 & s1 & S2 & s3 

  15:  

  16: End Function 

Function: Convert column letter to number (“B” –> 2)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. "B" --> 2) 

   3: '   Parameters:     The column letter (String) 

   4: '   Return:         The column number (Long) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnLetterToNumber(ByVal ColumnLetter As String) As Long 

   7:  

   8:     Dim intCount                As Integer 

   9:     Dim intColumnLetterLength   As Integer 

  10:     m_ConvertColumnLetterToNumber = 0: intColumnLetterLength = Len(ColumnLetter) 

  11:     For intCount = 1 To intColumnLetterLength 

  12:         m_ConvertColumnLetterToNumber = m_ConvertColumnLetterToNumber * 26 + (Asc(UCase(Mid(ColumnLetter, intCount, 1))) - 64) 

  13:     Next intCount 

  14:  

  15: End Function