# Thread: 4 digit PIN creation (97-->)

1. ## 4 digit PIN creation (97-->)

I have been requested to make a 4 digit PIN generator. The requirements are for the characters a-z (lower case) and 0-9 to be included in the PIN set. This gives me 1679616 (36^4) combinations. This is within limits of the requirement.

Now here is my conundrum:

0-9 is char(48)-char(57) inclusive, and
a-z is char(97)-char(122) inclusive

Now, here are my initial thoughts. I was thinking of approaching this as having 4 seperate characters and then concatenating them as a string of 4.
How can I loop through looking for a random number for 48-57 and 97-122 to assign the character. Am I going about this the wrong way and if so what are your suggestions?

2. ## Re: 4 digit PIN creation (97-->)

In the Analysis toolpak, there is a RandBetween function that generates a number between to values that you can specify!

Once the random numbers are generated, you can concatenate them, and use data validation on the range of results to check that no duplicate has been made! The formula you can use in the validation is =COUNTIF(\$A\$1:\$A\$100,A1)=1 where A100 is the last cell reference of the list of PINS.

3. ## Re: 4 digit PIN creation (97-->)

How about a simple formula like:
=MID("0123456789abcdefghijklmnopqrstuvwxyz",INT(RA ND()*36+1),1)&MID("0123456789abcdefghijklmnopqrstu vwxyz",INT(RAND()*36+1),1)&MID("0123456789abcdefgh ijklmnopqrstuvwxyz",INT(RAND()*36+1),1)&MID("01234 56789abcdefghijklmnopqrstuvwxyz",INT(RAND()*36+1), 1)

You could store the string: "0123456789abcdefghijklmnopqrstuvwxyz" in a name or cell if desired and use RANDBetween (if you know the analysis toolpack is installed) instead of the rand function I used.

Steve

4. ## Re: 4 digit PIN creation (97-->)

Thanks Rudi

That is useful but I'm not sure it is quite what I want (I think), randbetween(1,100) will give me a random number between 1 and 100 but I want a random number between 48-57 or 97-122, I tried the a range =randbetween(I1:I10,K1:K26) and got a #VALUE! error.

Any ideas what I am doing wrong?

5. ## Re: 4 digit PIN creation (97-->)

Cor, Now we're cooking with gas, thank the lord for cut and paste, it must have taken you 19 minutes just to type it <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

I think I will store the string in a name, good idea. What I like is you have made the answer so simple by using the MID function. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

7. ## Re: 4 digit PIN creation (97-->)

Thanks Rudi

That is good but it reduces the permutations by a factor of approximately 31. It is still usable as I have to generate potentially 36000 PINs. Thanks for your help, it will teach me to be more aware of the functions in my Analysis ToolPak <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

8. ## Re: 4 digit PIN creation (97-->)

I created the string of the letters (just typed them all out) and the rand function. I then created the mid function using it. Once 1 was created, copy and paste 3 times to make 4 and add the 3 ampersands...

Steve

9. ## Re: 4 digit PIN creation (97-->)

Aahhaa,

let us raise a <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> to the programmer who first wrote the cut and paste code, <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It wasn't you was it?

Thanks Steve, works a dream

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•