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

ID: Q198144

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, versions 4.0, 5.0, 6.0

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".

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