Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Code

0 views
Skip to first unread message

Beeel

unread,
Aug 31, 2003, 4:30:40 AM8/31/03
to
Is there an easy way to allocate a different numerical value to each letter
of the alphabet. We us codes that consist of three letters and four
numerals.(eg UGK2847). I want to disguise the letters so that anyone
viewing my list will not recognise what it is. (eg they will see 9573142847)

beeel40


Ken Wright

unread,
Aug 31, 2003, 4:48:53 AM8/31/03
to
One way would be to simply have a list of the alphabet in say A1:A26, then a set of random numbers
in B1:B26, and then use some kind of VLOOKUP formula perhaps, combined with the MID function to
create a new string for you, eg:-

Assuming you put your original code in D1, then the following will generate a new code for you.

=VLOOKUP(LEFT(D1,1),A1:B26,2,0)&VLOOKUP(MID(D1,2,1),A1:B26,2,0)&VLOOKUP(MID(D1,3,1),A1:B26,2,0)&VL
OOKUP(MID(D1,4,1),A1:B26,2,0)&RIGHT(D1,LEN(D1)-4)

You would need to be careful when creating your list of random numbers, as you would either need
to ensure that they were all the same length, so that it was easy to decipher, or, ensure that
none of your numbers are subsets of each other. For example, if you had 6, 12 and 26 as 3 of your
numbers, you might struggle to decide whether it should be 12 and 6 in your code or 1 and 26.

Either way it is only a simple solution, and whether you use it should depend on what this is
really for, and how secure this needs to be.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"Beeel" <ble...@xtra.co.nz> wrote in message news:OhOi4n5b...@TK2MSFTNGP11.phx.gbl...

Ken Wright

unread,
Aug 31, 2003, 5:04:45 AM8/31/03
to
And if I'd read the damn note properly, I would have seen you only said 3 Characters. Amended
formula as follows:-

=VLOOKUP(LEFT(D1,1),A1:B26,2,0)&VLOOKUP(MID(D1,2,1),A1:B26,2,0)&VLOOKUP(MID(D1,3,1),A1:B26,2,0)&RI
GHT(D1,LEN(D1)-3)


Also, if you'd rather keep the list somewhat hidden, then define put your data in A1:B26, go into
another cell, type =A1:B26, then hit F2 then F9. Copy the contents of the cell, then do Insert /
Name / Define, Type a names uch as Codes and in the cell reference box, paste the contents of your
cell, eg something like this:-

={"A",66;"B",68;"C",33;"D",35;"E",84;"F",82;"G",63;"H",98;"I",91;"J",30;"K",72;"L",26;"M",87;
"N",58;"O",12;"P",99;"Q",70;"R",11;"S",61;"T",19;"U",13;"V",81;"W",25;"X",14;"Y",36;"Z",44}

Now just amend your formula to the following:-

=VLOOKUP(LEFT(D1,1),Codes,2,0)&VLOOKUP(MID(D1,2,1),Codes,2,0)&VLOOKUP(MID(D1,3,1),Codes,2,0)&RIGHT
(D1,LEN(D1)-4)

Obviously you would need to hardcode those numbers before you put them on anything else, otherwise
they would bomb out, and once you were done and needed to reverse engineer them you could use
something like the following:-

Assuming all code numbers were 2 digits in length, and that the code you need to reverse is in
D3:-

=INDEX(A1:A26,MATCH(--MID(D3,1,2),B1:B26,0))&INDEX(A1:A26,MATCH(--MID(D3,3,2),B1:B26,0))&INDEX(A1:
A26,MATCH(--MID(D3,5,2),B1:B26,0))&RIGHT(D3,LEN(D3)-6)

You should have the gist now if you'd rather put named references in.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:OAS23y5b...@TK2MSFTNGP10.phx.gbl...

Ken Wright

unread,
Aug 31, 2003, 5:20:34 AM8/31/03
to
For crying out loud, somebody shoot me please:-

>
=VLOOKUP(LEFT(D1,1),Codes,2,0)&VLOOKUP(MID(D1,2,1),Codes,2,0)&VLOOKUP(MID(D1,3,1),Codes,2,0)&RIGHT
> (D1,LEN(D1)-4)

Should be

=VLOOKUP(LEFT(D1,1),Codes,2,0)&VLOOKUP(MID(D1,2,1),Codes,2,0)&VLOOKUP(MID(D1,3,1),Codes,2,0)&RIGHT
(D1,LEN(D1)-3)

I forgot to change the 4 to a 3 at the end.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message

news:OMAqv75b...@TK2MSFTNGP11.phx.gbl...

Don Guillett

unread,
Aug 31, 2003, 8:00:36 AM8/31/03
to
"Bang"

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message

news:%23SgClE6...@TK2MSFTNGP12.phx.gbl...

> > > Attitude - A little thing that makes a BIG difference
> >
> --------------------------------------------------------------------------
--
> > >
> > >
> > >

Ken Wright

unread,
Aug 31, 2003, 8:31:11 AM8/31/03
to
LOL {Keels over and dies gracefully - "See - I can take my shots!!" :-> }

--
Regards
Ken.......................


"Don Guillett" <don...@281.com> wrote in message news:OJvT#d7bDH...@TK2MSFTNGP11.phx.gbl...

StarTrek an beyond

unread,
Aug 31, 2003, 11:18:34 AM8/31/03
to
I am so glad I am not the only one that is brain dead at times

"Don Guillett" <don...@281.com> wrote in message
news:OJvT#d7bDH...@TK2MSFTNGP11.phx.gbl...

Biff

unread,
Aug 31, 2003, 6:19:18 PM8/31/03
to
Hi Beeel,

Here's another alternative. Use the ASCII char codes for
the alpha characters.

With UGK2847 being in A1. Use this formula in say B1:

=CODE(LEFT(A1,1))&CODE(MID(A1,2,1))&CODE(MID(A1,3,1))&MID
(A1,4,4)

Returns - 8571752847

And to 'decode' it, use this formula:

=CHAR(LEFT(B1,2))&CHAR(MID(B1,3,2))&CHAR(MID(B1,5,2))&MID
(B1,7,4)

Returns - UGK2847

Biff

>.
>

0 new messages