Microsoft Excel - How to give numerical value for multiple alphabet in one cell in excel

how to give numerical value for multiple alphabet in one cell for excel

if i give 1520 in one cell the answer mustbe CEUD for example  c is 1 u2 (C1,U2,M3,B4,E5,R6,L7,A8,N9,0D)

Thanks

Anees

Thanks a ton boss, its working.

Regards

Anees

Boss,

I have a similar case , but this time numeric to numeric as below 1 values 3, 2 as 6 ........, sorry for the trouble can you please help me again.

1-3,2-6,3-7,4-9,5-1,6-8,7-2,8-0,9-4,0- 5 .

Anees

Hi,

Neither of the formulas working in Excel2003, only works in Excel2007 any other formula is there for Excel2003 , please advise.

Thanks in advance

Br

Ishaq

Dear Mr.Bruce,

Thanks for all the help , i have seen your reply for Mr.Ishaq we are not same i recommended him to post the problem.  i have  2 more request on the same topic, and one is other.

1, will i be able to make a D or Q to precede the result (ie) if i give 12345 the result would be Dcumbe

2, on the A1 cell must not accecpt more than 5 character or numerics , it should accecpt 12345 only and the result must be (preceded by a D or Q ) Dcumbe. 

3, May i know about you - If possible

Please reply and help me

Thanks Again

Anees Ahamed

Sorry Mr.Bruce,

One more question including this the total of questions will be 4.

4, If i give 12 (two digits) also in the cell A1 , the result must be a 5 character + a preding letter D or Q , two are actual and the remaining represents 0 the result must be QDDDCU or DDDDCU.

Please reply

Thanks

Anees

1-C,2-U,3-M,4-B,5-E,6-R,7-L,8-A,9-N,0-D

Dear Mr.Bruce,

Can you please help me on the below requests.

Thanks for all the help , i have seen your reply for Mr.Ishaq we are not same i recommended him to post the problem.  i have  3 more request on the same topic, and one is other.

1, will i be able to make a D or Q to precede the result (ie) if i give 12345 the result would be Dcumbe

2, on the A1 cell must not accecpt more than 5 character or numerics , it should accecpt 12345 only and the result must be (preceded by a D or Q ) Dcumbe. 

3, If i give 12 (two digits) also in the cell A1 , the result must be a 5 character + a preding letter D or Q , two are actual and the remaining represents 0 the result must be QDDDCU or DDDDCU. 

4, May i know about you - If possible

Please reply and help me

Thanks Again

Anees Ahamed

Helo boss,

Thanks for the reply, please give me the array formula.sorry for the trouble.infact i need to create some pseudo codes for the fare.which a layman can not understand , i was able to create the formula in five different cells.with your help now i am able to do it in one cell , what ever the code i create it must not exceed 5+1 (preceding D or Q).Hence if i set the cell Say A1  not to allow more than 5 characters obviously the result will not be more than 5 characters, after that i have to add manually the preceding letter D or Q , instead if the answer comes  with the preceding letter Q or D there will not be any manual insertion required .please help me.

thanks

Anees

Sir,

Its working perfect, sorry i did not specify it properly about the value of Q and D (in our industry) Q stands for commisonable and D as Nett. how ever both the code would precede five characters .and is the below  possible.

If i give 2 (two digits) also in the cell A1 , the result must be a 5 character + a preceding letter D or Q , two are actual and the remaining represents 0 the result must be QDDDUD or DDDDUD.   

if a value of a product is $20 - i would enter in the cell (A1) as 20 but the result must be DDDDUD (D00020)

Please advise

Thanks

Anees

Sir,

Amazing works perfectly,Thanks again,  i will assaign different cell for Q and D, so it wont be a problem. May i know about you , you have any website ....... to know  or any E-mail id.

Thanks

Anees