Excel 2008 : Codeword conversion tool

**topojones** · 07-10-2010, 11:15 PM

Hi all, I am trying to create a simple codeword decryption tool that will help automate converting several 15 and 16 digit numbers from their encoded letters to their actual numbers. I would like it to also be able to distinguish between letters and numbers and if the value is a letter return the correlated number, if it is a number then simply return that number.

As an example if using

a n g l e r f i s h =
1 2 3 4 5 6 7 8 9 0

where each letter corresponds to a number value I would like to be able to decrypt "aglenfhishg4175 to 134527089034175.

I would also like to be able to enter the alphanumeric value in one single cell and have it displayed in another single cell.

Any ideas on the easiest way to do this, I would prefer a formula as I am not real familiar with VB but am willing to give it the college try with a little assistance.

Thanks to anyone who can help me crack the code on doing this the 'ol manual way. It gets old quick

Cheers,
TJ

**DonkeyOte** · 07-11-2010, 02:18 AM

So do you only have 10 valid alphas ?

If as implied you have 16 chars then you can't store the result as a number per se given XL is limited to 15 significant digits

It's not really clear if you mean that there will be 15/16 digits in addition to alphas or in fact the 15/16 value equates to characters...

If you want to avoid VBA [you have to if you're using 2008 as VBA is not supported] I would suggest first creating a lookup table like so:

NOTES:

-- it is imperative that the numerics in E1:E10 are stored as Text and not numbers - ie '1 rather than 1
-- it is imperative that the data is sorted alphabetically

With the above in place, let's assume for sake of demo. in E1:F20, then assuming your alpha numeric string is in A1 then to get the numerical equivalent in B1:

=LEFT(SUMPRODUCT(LOOKUP(MID(A1&REPT("0",MAX(0,15-LEN(A1))),ROW(A$1:A$15),1),$E$1:$F$20),10^(15-ROW(A$1:A$15)))&SUMPRODUCT(LOOKUP(MID(A1&REPT("0",MAX(0,30-LEN(A1))),ROW(A$16:A$30),1),$E$1:$F$20),10^(15-ROW(A$1:A$15))),LEN(A1))

the above can be copied down if you have additional strings to "decode" in A2, A3 etc...

(if you don't want to store the lookup table in a range you can store via an inline array constant)

**topojones** · 07-13-2010, 12:39 AM

Ah yes, sorry for the confusion. There are in fact only 10 available alphas and the cell will not have to be stored as a number per se. The end result is that I will have a completely numeric string (of which does not need to be stored as a number, it could be stored as text so long as it is displayed in it's correct numeric sequence) which is derived from an alphanumeric code where each value entered has a corresponding numeric value from 1 to 0.

I have been playing with the formula and for the life of me can only get the alpha characters to convert even when storing the numeric values as text. so given "anglerfish12345" I simply get "123456789000000" not sure what i am messing up here.

Thanks for your help by the way its farther than I have been able to get and I really appreciate it.

Cheers,
TJ

**DonkeyOte** · 07-13-2010, 02:18 AM

Originally Posted by topojones

for the life of me can only get the alpha characters to convert even when storing the numeric values as text. so given "anglerfish12345" I simply get "123456789000000" not sure what i am messing up here.

Originally Posted by D.O

NOTES:
-- it is imperative that the numerics in E1:E10 are stored as Text and not numbers - ie '1 rather than 1

The LOOKUP formula looks for 0-9 digits as text strings as opposed to numbers.
As a result the criteria values in the reference table (E1:E20 in my ex.) must all be text strings ... ie digits stored as text rather than number - you can do this either by:

a) entering ' before each number as you enter them ('1, '2 etc...)

b) formatting E1:E20 as Text before entering the values

The adjacent numeric values in F1:F20 (ie their "value") should be numbers of course.

You could tweak the formula to avoid the need for storing 0-9 criteria as text but it would require additional calculations and given this can be avoided quite simply is (IMO) best avoided.