Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)


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?

share|improve this question
Is the text to be decoded always 10 characters? – Rachel Hettinger Dec 4 '11 at 7:10
Does A1 contain 1 or the string A=1? – jonsca Dec 4 '11 at 7:19
usually the text will be 22 digits – maas Dec 4 '11 at 7:21
For more clarification cell A1: contains A and cell B1 contains the value which is 1 – maas Dec 4 '11 at 7:22
Are you open to using a custom VBA function? – Rachel Hettinger Dec 4 '11 at 7:30
up vote 1 down vote accepted

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
share|improve this answer
Where should I place it? – maas Dec 4 '11 at 8:18
Put the code in a regular module (not a class module that is tied to a sheet)--go to the VB Editor, Insert menu, and click Module. – Rachel Hettinger Dec 4 '11 at 8:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .