Microsoft KB Archive/833402

= How to convert Excel column numbers into alphabetical characters =

Article ID: 833402

Article Last Modified on 11/13/2006

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-





INTRODUCTION
This article discusses how to use the Microsoft Visual Basic for Applications (VBA) function in Microsoft Excel to convert column numbers into their corresponding alphabetical character designator for the same column.

For example, the column number 30 is converted into the equivalent alphabetical characters &quot;AD&quot;.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

The ConvertToLetter function works by using the following algorithm:
 * 1) Divide the column number by 27, and then put the resulting integer in the variable &quot;i&quot;.
 * 2) Subtract the column number from &quot;i&quot; multiplied by 26, and then put the result in the variable &quot;j&quot;.
 * 3) Convert the integer values into their corresponding alphabetical characters, &quot;i&quot; and &quot;j&quot; will range from 0 to 26 respectively.

For example: The column number is 30.
 * 1) The column number is divided by 27: 30 / 27 = 1.1111, rounded down by the Int function to &quot;1&quot;.

i = 1
 * 1) Next Column number - (i * 26) = 30 -(1 * 26) = 30 - 26 = 4.

j = 4
 * 1) Convert the values to alphabetical characters separately,

i = 1 = &quot;A&quot;

j = 4 = &quot;D&quot;
 * 1) Combined together, they form the column designator &quot;AD&quot;.

The following VBA function is just one way to convert column number values into their equivalent alphabetical characters: Function ConvertToLetter(iCol As Integer) As String Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = Int(iCol / 27) iRemainder = iCol - (iAlpha * 26) If iAlpha > 0 Then ConvertToLetter = Chr(iAlpha + 64) End If  If iRemainder > 0 Then ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64) End If End Function Note This function only converts integers that are passed to it into their equivalent alphanumeric text character. It does not change the appearance of the column or the row headings on the physical worksheet.

Additional query words: XL2007 xl xl97 xl2000 xl2002 xl2003

Keywords: kbcodesnippet kbcode kbhowto KB833402

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.