assign a numerical value to alphabets

Hi,
I have problems with assigning a numeric value to some letters. i want to use these letters instead of numbers. Is it possible for the excel to read a letter as number like:
I=0
J=1
K=2
L=3
M=4
N=5
O=6
P=7
Q=8
R=9
AND when i type or enter III IN A1 THEN B1 SHOULD SOW THE VALUE OF A1.. i.e, FOR EXAMPLE IF I WOULD ENTER III IN A1 THEN B2 SHOULD SHOW 000, IF I WOULD TYPE OR ENTER IJK IN A1 THEN B2 SHOULD SHOW 012

Please help me with this.
Thank you very much

Assuming you're always gonna enter a 3 character reference
this will work
=CODE(MID(A1,1,1))-73&CODE(MID(A1,2,1))-73&CODE(MID(A1,3,1))-73

Its based on ASCII codes rather than a lookup table

Or

=TEXT(SUMPRODUCT(CODE(MID(A1, {1,2,3}, 1))-73, {100,10,1}), "000")

saddamkafil,

Welcome to the Excel Forum.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Please Login or Register  to view this content.

To use the Function TxtToNbrV2:

If cell A1 = IJK

The function in cell B1
=TxtToNbrV2(A1)

B1 will display 012

late to the game, and based on @shg's idea, this is a slightly more "elastic" approach: