INFO: Convert (row,col) Indices into Excel-Style A1:C1 Strings

ID: Q198144

The information in this article applies to:

SUMMARY

Sometimes it is necessary to convert (row,column) numerical indices into Excel-style "A1:C1" string notation. This can be confusing because after 'Z', Excel starts using 'BA', 'BB', and so forth. This article contains a function that you can use in your code to do this conversion for you.


MORE INFORMATION

Microsoft Visual Basic Code: 


   ' Converts (row,col) indices to an Excel-style A1:C1 string
    Function IndexToString(row As Long, col As Long) As String
       IndexToString = ""
       If col > 26 Then
           IndexToString = Chr(Asc("A") + Int((col - 1) / 26) - 1)
       End If

       IndexToString = IndexToString & Chr(Asc("A") + ((col - 1) Mod 26))
       IndexToString = IndexToString & row
   End Function

Here are a few examples of the conversion: 

   Calling IndexToString() with row=1 and col=26 yields "Z1".
   Calling IndexToString() with row=1 and col=27 yields "AA1".
   Calling IndexToString() with row=2 and col=52 yields "AZ2".
   Calling IndexToString() with row=2 and col=53 yields "BA2".
   Calling IndexToString() with row=10 and col=10 yields "J10".
(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Joe Crump, Microsoft Corporation.

Additional query words: excel convert 


Keywords          : kbsample kbExcel KbVBA kbVBp kbVBp400 kbVBp500 kbVBp600 
Version           : WINDOWS:4.0,5.0,6.0
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: June 21, 1999