Convert Given Number to String Code

[29sandesh]

I have coded the price numbers against alphabet below, so if I put price as 9456 in input cell, I should get value AOSP in output cell. Please also keep in mind the ZERO its troubling me in formulas.

How can I do this, please help

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]M[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]H[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]O[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"]S[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"]P[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63"]I[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"]T[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63"]L[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Put 0 first in your list, and try

=LOOKUP(MID(A1,1,1)+0,E1:F10)&LOOKUP(MID(A1,2,1)+0,E1:F10)&LOOKUP(MID(A1,3,1)+0,E1:F10)&LOOKUP(MID(A1,4,1)+0,E1:F10)

Excel 2010
	A	B	C	D	E	F
1	9456	AOSP			0	L
2					1	K
3					2	M
4					3	H
5					4	O
6					5	S
7					6	P
8					7	I
9					8	T
10					9	A
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(MID(A1,1,1)+0,E1:F10)&LOOKUP(MID(A1,2,1)+0,E1:F10)&LOOKUP(MID(A1,3,1)+0,E1:F10)&LOOKUP(MID(A1,4,1)+0,E1:F10)

 

[29sandesh]

Only works for 4 digit numbers, what if price is single digit/double/triple eg 456

Put 0 first in your list, and try

=LOOKUP(MID(A1,1,1)+0,E1:F10)&LOOKUP(MID(A1,2,1)+0,E1:F10)&LOOKUP(MID(A1,3,1)+0,E1:F10)&LOOKUP(MID(A1,4,1)+0,E1:F10)


Excel 2010

	A	B	C	D	E	F
AOSP	L
K
M
H
O
S
P
I
T
A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]9456[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=LOOKUP(MID(A1,1,1)+0,E1:F10)&LOOKUP(MID(A1,2,1)+0,E1:F10)&LOOKUP(MID(A1,3,1)+0,E1:F10)&LOOKUP(MID(A1,4,1)+0,E1:F10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

What are the limits of how many digits?
At least 1, no more than 4 ?
And which version of Excel are you using?

 

[29sandesh]

Yes, atleast 1 and no more than 4 thanks. Excel 2010. Please help

 

[Jonmo1]

Try
=LOOKUP(MID(A1,1,1)+0,E1:F10)&IFERROR(LOOKUP(MID(A1,2,1)+0,E1:F10),"")&IFERROR(LOOKUP(MID(A1,3,1)+0,E1:F10),"")&IFERROR(LOOKUP(MID(A1,4,1)+0,E1:F10),"")

 

[bosco_yip]

Or,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"L"),1,"K"),2,"M"),3,"H"),4,"O"),5,"S"),6,"P"),7,"I"),8,"T"),9,"A")

 

[29sandesh]

This works , thanks you are a champ

 

[29sandesh]

Thanks This is Cool stuff..so happy

 

[Jonmo1]

You're welcome.