Reputation: 1896
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
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
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