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

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?

-
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

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
``````
-
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