Convert alpha characters to numbers

[stephfr]

Hi

I need to convert data present in some cells to a numeric value (ideally at input time or on cell exit). The range is fixed as it relates to a user entering the numbers on a french keyboard but forgetting to use caps lock. I don't want to format the cell to only allow numeric values because the user will kill me if he keeps getting an error message.....

I need to convert the following:
& - 1
é - 2
" - 3
' - 4
( - 5
§ - 6
è - 7
! - 8
ç - 9
à - 0

I tried using the SUBSTITUTE function that I found on another website but it didn't work

Thanks
Steph

 

[stephfr]

thanks guys