Letters to Number

Excel can use numbers or letters to designate a column. The first cell in a work sheet can be addressed by Cells(1,1).Select or by range("A1").Select. The numeric column designation must be converted to a character to accommodate some VBA functions. Similarly, the character column designation must be converted to a number. I created two macros Number_To_Letters() and Letters_To_Number() to assist in the conversion.

Although the last Excel column is IV or 256 this function will convert larger values. The input character string should be checked for non-alphabetic characters before using this macro.

SAS Fifth Dimension Macro-Letters to Number

Review the following code, copy it to Excel and execute it.

 Function LETTERS_TO_NUMBER(LETTERS)
    LETTERS_TO_NUMBER = 0
    ABC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    For KK = 1 To Len(LETTERS)
        CHAR = Mid(LETTERS, Len(LETTERS) - KK + 1, 1)
        For II = 1 To 26
            If UCase(CHAR) = Mid(ABC, II, 1) Then
                LETTERS_TO_NUMBER = LETTERS_TO_NUMBER + (II * (26 ^ (KK - 1)))
            End If
        Next
    Next
 End Function