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.
If the price code is in cell A2, enter this formula in cell B2:

```
=SUMPRODUCT(IFERROR(SEARCH(MID(A2,{1,2,3,4,5},1),"fbagojlce"),0)*{10000,1000,100,10,1})

```