Saturday 28 May 2011

Using VBA to Convert a Column Number To A Letter

Sometimes while manipulating data in Excel you’ll have a integer which represents the column number, but need the column letter.

The function below can translate that number to the column letter required.  This function has been tested and works to 702, column ‘ZZ’.

Function ConvertToLetter(iCol As Integer) As String
'-----------------------------------------------------------------------------
' Procedure : ConvertToLetter
' Author    : Niall - Zypher.co.uk
' Purpose   : Convert a number to a column letter
'             Tested to 256 columns, assumed to work until 702 (ZZ)
'-----------------------------------------------------------------------------
'
    If iCol <= 26 Then
        ' Columns A-Z
        ConvertToLetter = Chr(iCol + 64)
    Else
        ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & _
                          Chr(((iCol - 1) Mod 26) + 65)
    End If
End Function

As always, any questions or enquiries let us know via the comments section.

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Doesn't work to ZZ, for example BA, is instead B[ or something like that, because the "chr" includes non-alphabets as well, any idea how to fix this?

    ReplyDelete
  3. x = 1 ' or any number do you need :)
    Cells(1, x).Value = Split(Cells(1, x).Address, "$")(1)

    ReplyDelete
  4. Your website is really cool and this is a great inspiring article.
    visual basic course london

    ReplyDelete

Please, no purely anonymous comments, they will be deleted; always use a name for ease of reference by other commenters.