Code

  • Thread starter Thread starter Beeel
  • Start date Start date
B

Beeel

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
 
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.
 
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.
 
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)
Click to expand...

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.
 
"Bang"

Ken Wright said:
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

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
-------------------------------------------------------------------------- --




=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 your data in A1:B26, go
into the cell, then do Insert
/ paste the contents of
your ={"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}
=VLOOKUP(LEFT(D1,1),Codes,2,0)&VLOOKUP(MID(D1,2,1),Codes,2,0)&VLOOKUP(MID(D1
,3,1),Codes,2,0)&RIGHT on anything else,
otherwise
=INDEX(A1:A26,MATCH(--MID(D3,1,2),B1:B26,0))&INDEX(A1:A26,MATCH(--MID(D3,3,2
),B1:B26,0))&INDEX(A1:
--
-- then a set of random
numbe with the MID function
to 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 numbers, as you would either
need
Click to expand...
 
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
 
Back
Top