Function to convert Excel column into number

  • Thread starter Thread starter Chlaris
  • Start date Start date
C

Chlaris

Dear all,

I need a function to convert Excel column into number.
Example :

A = 1
B = 2
C = 2
Z = 26
AA = 27
AB = 28
AZ = 28
BA = 53
BB = 54

Please help. Thanks.

Chlaris
 
Public Function XLSColToNum(ByVal strCell As String) As Integer

Dim strCol As String
Dim strChar As String
Dim bytPos As Byte
Dim intAsc As Integer
Dim intNum As Integer

For bytPos = 1 To Len(strCell)
intAsc = Asc(UCase(Mid(strCell, bytPos, 1)))
If intAsc < 65 Or intAsc > 90 Then
Exit For
End If
Next
If bytPos > 1 Then
strCol = UCase(Left(strCell, bytPos - 1))

intAsc = Asc(UCase(Right(strCol, 1))) - 64
If Len(strCol) > 1 Then
intAsc = intAsc + (Asc(UCase(Left(strCol, 1))) - 64) * 26
End If
End If

XLSColToNum = intAsc
End Function
 
Back
Top