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


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 4.0
  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
This article was previously published under Q198144

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 1999, All Rights Reserved. Contributions by Joe Crump, Microsoft Corporation.

Modification Type:MinorLast Reviewed:3/21/2005
Keywords:kbinfo kbSample KB198144
©2004 Microsoft Corporation. All rights reserved.