Question: I have some price codes, for example `BBAXV` and `GGZTR`, to convert to numbers, respectively `22300` and `44000`, according to these rules: 

```
F > 1  
B > 2  
A > 3  
G > 4  
O > 5  
J > 6  
L > 7  
C > 8  
E > 9  
Else > 0
```

I'm currently using VBA: 

```vb
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
    Dim i As Single
    For i = 1 To old_text.Cells.Count
        Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
        text = Result
    Next i
    SubstituteMultiple = Result
    End Function

```

with for example:

```
=SubstituteMultiple(A2,$D$2:$D$3,$E$2:$E$3)

```

Is there instead any simple formula I can use for this?

Answer: 
Any length (within reason)

```
=SUM(10^(LEN(A1)-ROW(INDIRECT("$1:$" & LEN(A1)))) *  
  IFERROR(SEARCH(MID(A1,ROW(INDIRECT("$1:$" & LEN(A1))),1),"FBAGOJLCE"),0))

```

Ctrl+Shift+Enter since it's an array formula.
These two User Defined Function (aka UDF) roughly reverse the function provided by the answer in [this](https://stackoverflow.com/questions/32667180/convert-numeric-characters-to-alphabetic-characters/32667494#32667494).

```
Function alpha_to_num(str As String)
    Dim c As Long, tmp As String, vRPL As Variant

    vRPL = Array(3, 2, 8, 0, 9, 1, 4, 0, 0, 6, 0, 7, 0, 0, 5)
    tmp = UCase(str)

    For c = 1 To Len(tmp)
        Select Case Asc(Mid$(tmp, c, 1))
            Case 65, 66, 67, 69, 70, 71, 74, 76, 79
                tmp = Replace(tmp, Mid$(tmp, c, 1), vRPL(Asc(Mid$(tmp, c, 1)) - 65))
            Case Is > 57
                tmp = Replace(tmp, Mid$(tmp, c, 1), 0)
            Case Else
                'do nothing
        End Select
    Next c

    alpha_to_num = tmp

End Function

Function alpha_to_num_two(str As String)
    Dim c As Long, tmp As String

    tmp = "fbagojlce"

    For c = 1 To Len(str)
        If Asc(Mid$(str, c, 1)) > 57 Then _
            str = Replace$(str, Mid$(str, c, 1), InStr(1, tmp, Mid$(str, c, 1), vbTextCompare))
    Next c

    alpha_to_num_two = str

End Function

```

Both have has the advantage of not overwriting replacements.