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]

try this....
Excel 2010

	X	Y
1	MAPPING
2	A	1
3	B	2
4	C	3
5	D	4
6	E	5
7	F	8
8	G	3
9	H	5
10	I	1
11	J	1
12	K	2
13	L	3
14	M	4
15	N	5
16	O	7
17	P	8
18	Q	1
19	R	2
20	S	3
21	T	4
22	U	6
23	V	6
24	W	6
25	X	5
26	Y	1
27	Z	7

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D
1	INPUT	OUTPUT	CAN HIDE
2	LN	35	3	5
3	LNO	357	3	5
4	lmnporsqkt	3458723124	3	4

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

You will need to drag Column D over for your 10 digits....

Worksheet Formulas Cell Formula B2 =LEFT(C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2,LEN(A2)) C2 =IF(ISERROR(VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)) D2 =IF(ISERROR(VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)) B3 =LEFT(C3&D3&E3&F3&G3&H3&I3&J3&K3&L3&M3&N3&O3&P3&Q3&R3&S3&T3,LEN(A3)) C3 =IF(ISERROR(VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)) D3 =IF(ISERROR(VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)) B4 =LEFT(C4&D4&E4&F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4&Q4&R4&S4&T4,LEN(A4)) C4 =IF(ISERROR(VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)) D4 =IF(ISERROR(VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To mapping =Sheet1!$X$2:$Y$27 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[kalpeshkansara]

First of all I am very thankful to you that you replied or tried your experience or precious time for me.

I tried this but its not like that as i need. I need only for that letters which i shown and not for full alphabets. pls help if you have any formula for that only. not for full alphabets.

try this....
Excel 2010

	X	Y
1	MAPPING
2	A	1
3	B	2
4	C	3
5	D	4
6	E	5
7	F	8
8	G	3
9	H	5
10	I	1
11	J	1
12	K	2
13	L	3
14	M	4
15	N	5
16	O	7
17	P	8
18	Q	1
19	R	2
20	S	3
21	T	4
22	U	6
23	V	6
24	W	6
25	X	5
26	Y	1
27	Z	7

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D
1	INPUT	OUTPUT	CAN HIDE
2	LN	35	3	5
3	LNO	357	3	5
4	lmnporsqkt	3458723124	3	4

<tbody>
</tbody>

Sheet1​

You will need to drag Column D over for your 10 digits....

Worksheet Formulas Cell Formula B2 =LEFT(C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2,LEN(A2)) C2 =IF(ISERROR(VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)) D2 =IF(ISERROR(VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A2,COLUMN()-2,1),mapping,2,FALSE)) B3 =LEFT(C3&D3&E3&F3&G3&H3&I3&J3&K3&L3&M3&N3&O3&P3&Q3&R3&S3&T3,LEN(A3)) C3 =IF(ISERROR(VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)) D3 =IF(ISERROR(VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A3,COLUMN()-2,1),mapping,2,FALSE)) B4 =LEFT(C4&D4&E4&F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4&Q4&R4&S4&T4,LEN(A4)) C4 =IF(ISERROR(VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)) D4 =IF(ISERROR(VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)),0,VLOOKUP(MID($A4,COLUMN()-2,1),mapping,2,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To mapping =Sheet1!$X$2:$Y$27 <tbody> </tbody>

<tbody>
</tbody>

 

[keiserj]

You should just be able to delete cells in the Mapping range that you don't need.

 

[keiserj]

Excel 2010

	X	Y
1	MAPPING
2	K	1
3	L	2
4	M	3
5	N	4
6	O	5
7	P	6
8	Q	7
9	R	8
10	S	9
11	T	0

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[István Hirsch]

Do you want the number while you are typing in the cell or when you left the cell?

 

[kalpeshkansara]

Yes I need numbers in result when I type any letters of that question and see in example Fig.1 and 2. When i type only one letter from given alphabets say for example if I type "N" in C3 cell then I need number of that matching in C2 cell. same as well as When i type to letters as if "MP" in C3 cell then it must come "36" in C2 cell. and same as well if i type any letters as given in question starting from top to end , it must come numbers respectively shown in their pair from left to right side. Means if i type letters "STMPQL" then it must come same as i typed "903672"

 

[kalpeshkansara]

Dear from where I do mapping ? can u explain me in details or email me your sample file to my email id at kalpeshkansara@yahoo.com.

 

[keiserj]

"Mapping" is just the name I gave the range of cells that had the data in it (you can name it what ever). This way you can put it on any sheet in the workbook. All the formulas does is look up in the table (which i called Mapping) and reference the Letter to number.

 

[kalpeshkansara]

See I have tried in my excel sheet. and now tell what and where is wrong? here I attaching image of it.