CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1896

Converting letters to numbers in VBA

Looking for some suggestions. I have cell values with a combination of letters and numbers in a cell. Each value is 6 letters, followed by 0000 followed by 4 letters and then between 5 and 8 numbers. E.G. 

IIKBAR0000EEEE510002
MQYUQF0000EEEE410003
MWWVIQ0000ZYXW210004
DJHZXL0000BBBB410005

These are more or less completely random, and there are between 100k and 1,048,576 of them!

Is there any way that I can convert the letters into a number (I.e. A = 1, B = 2, Z = 26, etc.) without having to tell VBA the value of each letter? the code I am currently using is quite slow as it has 26 If-Else cases.

Upvotes: 1

Views: 24300

Answers (2)

elliot svensson
elliot svensson

Reputation: 603

Here, I wrote this. No range call required:

Function converttonumber(Column as string) As Long

    Dim A As Integer, currentletter As String

    converttonumber = 0
    For A = 1 To Len(Column)
        currentletter = Mid(Column, Len(Column) - A + 1, 1)
        converttonumber = converttonumber + (Asc(currentletter) - 64) * 26 ^ (A - 1)
    Next

End Function

Sub test()
    Debug.Print converttonumber("AZ")
    Debug.Print converttonumber("E")
    Debug.Print converttonumber("AAA")
End Sub

Upvotes: 3

Brian
Brian

Reputation: 2108

I think this UDF will work for you:

Option Explicit

Function ConvertLetterToNumber(ByVal strSource As String) As String

Dim i As Integer
Dim strResult As String

For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))
        Case 65 To 90:
            strResult = strResult & Asc(Mid(strSource, i, 1)) - 64
        Case Else
            strResult = strResult & Mid(strSource, i, 1)
    End Select
Next
ConvertLetterToNumber = strResult

End Function

It will convert each non-numeric value to its Ascii equivalent and then reduce it down to its alphabet equivalent (A=1, B=2, etc.). Just use it as a regular formula. NOTE: It assumes that your non-numeric values are upper case.

ADDENDUM

The code as stated above will return 250,000 rows in just under 2 minutes with a 4th Gen i5 processor and 8GB RAM.

Upvotes: 6

Related Questions