Need formula for my text in excel

[kalpeshkansara]

If I am using or considering some numbers I need from some selected alphabets I am putting that as below,

Where
K = 1
L = 2
M = 3
N = 4
O = 5
P = 6
Q = 7
R = 8
S = 9
T = 0

I need this alphabets in excel formula to get numbers of alphabet which i type in other cell. Or if I type any alphabets in 2 or any pairs that shows same numbers at right side of my typed alphabets in same cell address where i need it.

for example if i need to get numbers in

Fig.1 > 2 digits or 4 digits or may be 10 digits. like "24" or "245" or "2346589710". for this if I type in a specific cell
Fig.2 > "LN" or "LNO" or "LMNPORSQKT" then in result I need to get same numbers as I shown in Fig.1. from Fig.2.

pls explain or reply me formula in excel for this type of needs.

Pls help

 

[keiserj]

Have you defined a name of the range?

Select X2:Y11
Right Click
Select Define Name
Type in- Mapping

Plus you need to drag across to get your ten digits

 

[kalpeshkansara]

in Which cell address i have to define? and what is =Sheet1!$X$2:$Y$27 ?

 

[István Hirsch]

Give this a try, enter with Ctrl + Shift + Enter, not just Enter:

=LEFT(SUM((0+IFERROR(CHAR(CODE(MID(SUBSTITUTE(A1,"T","J"),{1,2,3,4,5,6,7,8,9,10},1))-26),0))*10^{9,8,7,6,5,4,3,2,1,0}),LEN(A1))

 

[kalpeshkansara]

Istvan Hirsch

Thanks for your kind approach and you replied my query.

In this formula I have tried but only first letter is answering in whole like if I type "KLT" then its answering "111". So somewhere is mistake that is not getting answer properly. Where it should come ans as "120".

 

[kalpeshkansara]

Thanks for your great help.

 

[kalpeshkansara]

Mr. Jason Keiser, You have solved my biggest problem. Great Mind. Thanks again.

 

[István Hirsch]

Istvan Hirsch

Thanks for your kind approach and you replied my query.

In this formula I have tried but only first letter is answering in whole like if I type "KLT" then its answering "111". So somewhere is mistake that is not getting answer properly. Where it should come ans as "120".

As I have mentioned, you should enter the formula with CTRL + SHIFT + ENTER, not just Enter.