Number encryption

[paulsolar]

Hi All

I'm after a bit of advice please. I expect someone has done this before.

I have a list of 1000's of unique numbers between 1 and 6 digits that I want to increase all of to 8 digits. The problem is that I cant keep the existing numbers in the same order as they would be easy to guess.

Ideally what I'd like to do is have something that will change the numbers to 8 digits with the numbers still remaining unique. And then a method so it can be reversed back to it's original format if required.

I've looked into moduls checking type methods and some other methods but they are a massive overkill for what I require. All I'm trying to do is convert existing membership numbers to a new format for membership cards that require 8 digits.

Kind regards

Paul

 

[wideboydixon]

Easiest way might be a simple BITXOR with some padding:

Book1
	A	B	C
1	Key:	00192837
2	Old Value	New Value	And back again
3	123456	12014776	123456
4	000534	01933634	000534
5	999999	98979149	999999
Sheet3

Cell Formulas
Range	Formula
B3	=INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10)
C3	=BITXOR(MOD(INT(B3/10),10^6),$B$1)

WBD

 

[paulsolar]

Brilliant thanks, I've never come across this before, what a brilliant function.

 

[paulsolar]

Hi WBD

I've managed the padding and formatting (probably a better way of doing it but it's working)

But I'm having a nightmare with the un-ecryption as I've never tried this before.

Sorry to be a pain, could you help me with this please.

cheers

Paul

="PI - "&IF(LEN(INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10))=7,"0"&INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10),IF(LEN(INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10))=6,"00"&INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10),IF(LEN(INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10))=8,INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10))))

 

[wideboydixon]

Yuck. I just created a custom format for the cells with eight zeroes. Use the original formula, then higlight the column that is supposed to be eight digits. Press Ctrl+1, go to the Number tab, select Custom and type in 00000000

That will pad the numbers for you.

WBD

 

[wideboydixon]

Book1
	A	B	C
1	Key:	192837
2	Old Value	New Value	And back again
3	123456	PI - 12014776	123456
4	534	PI - 01933634	534
5	999999	PI - 98979149	999999
Sheet1

Cell Formulas
Range	Formula
B3	="PI - "&TEXT(INT(A3/10^5)*10^7+BITXOR(A3,$B$1)*10+MOD(A3,10),"00000000")
C3	=BITXOR(MOD(INT(RIGHT(B3,8)/10),10^6),$B$1)

WBD

 

[paulsolar]

Hi

Compared to your solution "Yuck" is an understatement!!

Brilliant thanks, I'm going to read up on this tonight. looks like it may come in handy in the future.

Thanks again for your time

cheers

Paul

 

[paulsolar]

Hi Wideboy

I've just worked out how this is working, absolutely brilliant bit of work. To say I'm mega impressed would be an understatement.

cheers

Paul