How to replace letters in a cell with defined numbers from a separate table

Question

I have a cell which is has mixed numbers and letters(ex. AA12CC45WR) and they are random.

On another sheet, I have a table which has the equivalent for each letter like (cells A1, B1)

A=1
C=25
W=66
R=77 

for the ABC letters until Z

The solution should be 11122525456677

How do I generate a function to replace all the letters with the defined numbers in the other sheet?

Answer 1

Here's a function that will do the decoding. Place this in a module and update the Range reference as necessary. If cell A1 has original "code" value, enter the formula: =Decode(A1) in another cell to get the "decoded" value.

Public Function Decode(Code As String) As String
    Dim iIdx As Integer
    Dim sChar As String
    Dim sResult As String
    For iIdx = 1 To Len(Code)
        sChar = UCase$(Mid$(Code, iIdx, 1))
        If Asc(sChar) >= 65 And Asc(sChar) <= 90 Then
            sChar = WorksheetFunction.VLookup(sChar, Worksheets("Sheet2").Range("A1:B26"), 2, False)
        End If
        sResult = sResult & sChar
    Next iIdx
    Decode = sResult
End Function