Decode with excel

[Yurigas]

Hi,
I would like to know if I can decode an alphabetic code with Excel.
Eg.
This is the type code that should decode:

ACAFDEQZPO

I would break it down into parts such

AC AFDE QZ PO

and each part assigned a corresponding meaning, reading from a list over, in other columns, or sheets

AA-Prova1 AAAA-Testo1 AA-Sing1 AA-Paro1
AB-Prova2 AAAB-Testo2 AB-Sing2 AB-Paro2
AC-Prova3 . . .
AD-Prova4 . . .
AF-Prova5 AFDE-Teston QZ-Singn PO-Paron
. . . .
. . . .
. . . .
. . . .
ZZ-Provaz ZZZZ-Testoz ZZ-Singz ZZ-Paroz

Final result on another sheet or on other columns

A1 B1 C1 D1
Prova3 Teston Singn Paron

Best regards

Yuri <!-- / message -->

 

[bertie]

If your code string is a constant 10 characters and breaks down into substrings of 2-4-2-2 characters you can do this using vlookups and a combination of Left, Mid and Right Functions.

Sheet1 - Results

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 126px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">ACAFDEQZPO</TD><TD>Prova3</TD><TD>Testo3</TD><TD>Sing3</TD><TD>Paro3</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=VLOOKUP(LEFT(A1,2),$G$1:$H$3,2,FALSE)</TD></TR><TR><TD>C1</TD><TD>=VLOOKUP(MID(A1,3,4),$I$1:$J$3,2,FALSE)</TD></TR><TR><TD>D1</TD><TD>=VLOOKUP(MID(A1,7,2),$K$1:$L$3,2,FALSE)</TD></TR><TR><TD>E1</TD><TD>=VLOOKUP(RIGHT(A1,2),$M$1:$N$3,2,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Sheet1 - lookup ranges

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 46px"><COL style="WIDTH: 83px"><COL style="WIDTH: 43px"><COL style="WIDTH: 85px"><COL style="WIDTH: 43px"><COL style="WIDTH: 85px"><COL style="WIDTH: 49px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">AA</TD><TD>Prova1</TD><TD>AAAA</TD><TD>Testo1</TD><TD>AA</TD><TD>Sing1</TD><TD>AA</TD><TD>Paro1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">AB</TD><TD>Prova2</TD><TD>AAAB</TD><TD>Testo2</TD><TD>AB</TD><TD>Sing2</TD><TD>AB</TD><TD>Paro2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">AC</TD><TD>Prova3</TD><TD>AFDE</TD><TD>Testo3</TD><TD>QZ</TD><TD>Sing3</TD><TD>PO</TD><TD>Paro3</TD></TR></TBODY></TABLE>

Hope this helps.

 

[Yurigas]

Hi
Thank you for your help, i have excel 2007 just copying your sample I had some error I discovered the first, I correct

LEFT(A1,2) with LEFT(A1;2)

and

,$G$1:$H$3,2,FALSE) with ;$G$1:$H$3)

in this way not give error but obviously not work

and I do not find the function MID I have Italian version

Best regards

Yuri

 

[VoG]

Try STRINGA.ESTRAI

http://xlsgen.arstdesign.com/core/formulas.html

 

[Yurigas]

Ok, thank you very much this link isi very very useful

Best regards

Yuri

 

[Yurigas]

Sorry
But until now I can not understand how to use the suggestions that you gave me.
Can you be a little clearer?

However, no one has told me that Excel can not do what I need.
So i still studing.

Best regards
Yuri

 

[snowblizz]

Sorry
But until now I can not understand how to use the suggestions that you gave me.
Can you be a little clearer?

However, no one has told me that Excel can not do what I need.
So i still studing.

Best regards
Yuri

Hi, Yuri to fix the formulas do like this:

"English" formula
=VLOOKUP(LEFT(A1,2),$G$1:$H$3,2,FALSE)

"European"
=VLOOKUP(LEFT(A1;2);$G$1:$H$3;2;FALSE)

The things is most "european" versions of Excel use ';' where the "english" version uses ',' because we use different characters for decimals.
Also, use the link posted above to translate the formulas into Italian.

"Italian" version
=CERCA.VERT(SINISTRA(A1;2);$G$1:$H$3;2;FALSO)

 

[Yurigas]

Hi,
the formula with the fixing that you suggest me, works exactly like i want.Thank you very much to everybody.
Now that I've discovered the potential of the program, I will try to improve as much as possible my knowledge of Excel.

Best regards

Yuri