Convert letter to Number

**gtmeloney** · 07-23-2009, 10:34 AM

Is there simple function that anyone knows of (or has written) that will convert a letter to its alpha-numeric equivalent?

For instance, A = 1, B = 2, AA = 27, etc (a = 1, b = 2, aa = 27)

**jrussell** · 07-23-2009, 10:47 AM

Ran into this yesterday, no idea if it works:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=218

**gtmeloney** · 07-23-2009, 10:53 AM

Thanks J.

I learned the following trick from the link you sent:

    Dim nNum As Integer
    Dim str As String
    
    str = "d"
    
    nNum = (Range(str & "1").Column)
    
    MsgBox (nNum)

I will wrap that in a function and be on my way.

Thanks

**jrussell** · 07-23-2009, 10:54 AM

Whoops, ran into this too: http://exceltip.com/st/Convert_betwe...Excel/478.html

The function below converts a number between 1 and 256 to a column reference between A and IV: 
Function ColNo2ColRef(ColNo As Integer) As String
    If ColNo < 1 Or ColNo > 256 Then
        ColNo2ColRef = "#VALUE!"
        Exit Function
    End If
    ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
    ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function
The function below converts a column reference (A - IV) to a column number between 1 and 256: 
Function ColRef2ColNo(ColRef As String) As Integer
    ColRef2ColNo = 0
    On Error Resume Next
    ColRef2ColNo = Range(ColRef & "1").Column
End Function

**gtmeloney** · 07-23-2009, 10:55 AM

Thanks again J.

**Phil_V** · 07-23-2009, 11:04 AM

Incidently why are you wanting / needing to do this? If it is for referencing columns it can be done in it's numeric format...

**gtmeloney** · 07-23-2009, 11:10 AM

I store several column letters to display to the user, and now I need to take that list of Column letters and set a combobox according to the corresponding column number.

So if the column is A, I need to select the 0th index in the combobox, if the column is D, I need to select the 3rd index. Hence my need for the number conversion