Excel Encoding

i want to know if it is possible to change a Excel Cell Data to letters
for a examplei have a set of codes represents each letter
like
1=M , 2=O, 3=D , 4=E, 5=R , 6=N , 7=L , 8=I , 9=C , 0=A

so a cell date is 10 the answer should be MA Eg ( 1=M, A=0 ) 10=MA
so a cell date is 100 the answer should be MAA Eg ( 1=M, A=0 , A=0 ) 100=MAA
so a cell date is 500 the answer should be RAA Eg (5=R , A=0, A=0 500=RAA
so a cell data is 750 the answer should be LRA Eg (7=L , 5=R , A=0 ) 750 =LRA
so a cell data is 1750 the answer should be LRA Eg (1=M ,7=L , 5=R , A=0 ) 1750 =MLRA

encryption! Can this be Done?

MarvinP!
Promt Reply!
I wonder How to do this !!Any Helps ?

This is For Encoding Price of the Items Ranging from 0$ to 99999$
Using only 10 Alphabets .. Like "0-9" ,"10-99,"100-999","1000-99000".
I wonder How to Do this ,,,,,


my Email is [email protected] any tutorials ?

I have Uploaded the File

Using a table in A1:B10, the below works for up to 4 digits.

=IF(LEN(D1)=1,VLOOKUP(D1,A:B,2,0),IF(LEN(D1)=2,VLOOKUP(NUMBERVALUE(LEFT(D1,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,2,1)),A:B,2,0),IF(LEN(D1)=3,VLOOKUP(NUMBERVALUE(LEFT(D1,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,2,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,3,1)),A:B,2,0),IF(LEN(D1)=4,VLOOKUP(NUMBERVALUE(LEFT(D1,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,2,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,3,1)),A:B,2,0)&VLOOKUP(NUMBERVALUE(MID(D1,4,1)),A:B,2,0),""))))

Table:
1 M
2 O
3 D
4 E
5 R
6 N
7 L
8 I
9 C
0 A

Corrrected for your sheet. And 5 digits

=IF(LEN(B3)=1,VLOOKUP(B3,E:F,2,0),IF(LEN(B3)=2,VLOOKUP(NUMBERVALUE(LEFT(B3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,2,1)),E:F,2,0),IF(LEN(B3)=3,VLOOKUP(NUMBERVALUE(LEFT(B3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,2,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,3,1)),E:F,2,0),IF(LEN(B3)=4,VLOOKUP(NUMBERVALUE(LEFT(B3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,2,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,4,1)),E:F,2,0),IF(LEN(B3)=5,VLOOKUP(NUMBERVALUE(LEFT(B3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,2,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,3,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,4,1)),E:F,2,0)&VLOOKUP(NUMBERVALUE(MID(B3,5,1)),E:F,2,0),"")))))

Try this ...

Please Login or Register  to view this content.

Use:
=Encoding(B3,$E$2:$F$11)

I added Substitutes to MarvinP Code as Follows

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B3,"#"),"0","A"),"1","M"),"2","O"),"3","D"),"4","E"),"5","R"),"6","N"),"7","L"),"8","I"),"9","C")

This Code works like charm ,,,Thanks MarvinP! Thank you Everyone , This Forum is Amazing,,,,,,,,,, The members are Awesome.....Thumbs Up

Hello Members,
With the great help I achieved through this group, I would like to thanks everyone helped in overcome my previous problem related to Substitute Function..
As Per now, I have a very important Issue to discuss with the Great Team here and to get help if possible..
There are 3 sheets in my excel sheet.

1.Inventory (the stock details page)

2.Generator (The bar code generator using concatenate function using ccode39 font )

3. Print Page (page to print Barcode sticker in a4 sheet)

*screenshots and actual Excel File is attached
-The Stock details are entered in the inventory page and it holds the data of the stock. After entering the details of the stock in inventory page, the Generator page creates each and every barcode as per the Stock code using ccode 39 font followed with the item description using concatenate function and the price and the print sticker quantity.
Now the trick is in Print Page, after the data is created in generator page, the print sheet should be filed with the bar code in order to be printed on a A4 sheet including description and the price according to the number of sticker needed to print. I do this manually pasting it from the barcode page data to the print page which consumes more time and wrong sticker count as human error..
So I wonder if there is any way that this could be done automatically to past the details to the print page from generator to print sheet with actual number of stickers needed using the data form generator page. And the print sheet row cannot be filled more than four barcodes in a column as in sample ,as printer will not print if it goes beyond the print range..
Hope this sounds bit weird and need an inventory system to do my work, But still I try it do it with Excel as I love working in Excel like a boss….

Thanks..

Originally Posted by MarvinP

Mine is close to Phil's above..

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B3,"#"),"0","A"),"1","M"),"2","O"),"3","D"),"4","E"),"5","R")

You will need to add a few levels of substitute to get 6,7,8,9,

This code worked for me ,To change Numbers to code .......................
EG.. 101 = MAM

But HOW we do it other way round to change a Code to Text ??
eg... MAM = 101 ???

i tried editing code but it does not work..

Please Help..