# Need formula for my text in excel

#### kalpeshkansara

##### New Member
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### keiserj

##### Board Regular
try this....
Excel 2010
XY
1MAPPING
2A1
3B2
4C3
5D4
6E5
7F8
8G3
9H5
10I1
11J1
12K2
13L3
14M4
15N5
16O7
17P8
18Q1
19R2
20S3
21T4
22U6
23V6
24W6
25X5
26Y1
27Z7

<tbody>
</tbody>
Sheet1
Excel 2010
ABCD
1INPUTOUTPUTCAN HIDE
2LN3535
3LNO35735
4lmnporsqkt345872312434

</tbody>
Sheet1
You will need to drag Column D over for your 10 digits....

Worksheet Formulas
CellFormula
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>

Workbook Defined Names
NameRefers To
mapping=Sheet1!\$X\$2:\$Y\$27

</tbody>

<tbody>
</tbody>

#### kalpeshkansara

##### New Member
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
XY
1MAPPING
2A1
3B2
4C3
5D4
6E5
7F8
8G3
9H5
10I1
11J1
12K2
13L3
14M4
15N5
16O7
17P8
18Q1
19R2
20S3
21T4
22U6
23V6
24W6
25X5
26Y1
27Z7

<tbody>
</tbody>
Sheet1
Excel 2010
ABCD
1INPUTOUTPUTCAN HIDE
2LN3535
3LNO35735
4lmnporsqkt345872312434

<tbody>
</tbody>
Sheet1
You will need to drag Column D over for your 10 digits....

Worksheet Formulas
CellFormula
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
NameRefers To
mapping=Sheet1!\$X\$2:\$Y\$27

<tbody>
</tbody>

<tbody>
</tbody>

#### keiserj

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

Excel 2010
XY
1MAPPING
2K1
3L2
4M3
5N4
6O5
7P6
8Q7
9R8
10S9
11T0

</tbody>
Sheet1

#### István Hirsch

##### Well-known Member
Do you want the number while you are typing in the cell or when you left the cell?

#### kalpeshkansara

##### New Member
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

##### New Member
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

##### Board Regular
"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

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

Replies
4
Views
213
Replies
2
Views
179
Replies
7
Views
1K
Replies
0
Views
201
Replies
3
Views
143

1,195,853
Messages
6,011,969
Members
441,658
Latest member
Carlos O

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back