Create Alphanumeric Passwords

[simonwar]

Hi,

I would like to use an excel spreadsheet to generate a series of passwords. The password must include alphanumeric content, and be at least 10 characters long, and case sensitive.

For example: 14sH2dA456a

Thought about concatenting each variable, but do not know how to control the random variable creation in a spreadsheet?

I have looked at RAND but can't figure out how to use it to any avail?

Any help, much appreciated.

Thanks, Simon.

[Dave Hawley]

Add a to z in B1 to B25.

In C1 Enter =Upper(B1) and copy down. Now copy the results and Edit>Paste Special - Values. Name B1:C25 range Alphas.

Now use a formula like;

=INT(RAND()*1000) & INDEX(Alphas,INT(RAND()*25+1),INT(RAND()*2+1)) & INT(RAND()*1000) & INDEX(Alphas,INT(RAND()*25+1),INT(RAND()*2+1)) & INT(RAND()*1000) & INDEX(Alphas,INT(RAND()*25+1),INT(RAND()*2+1))

Be aware that all Random formulas are Volatile.

[filo65]

try as well:

in A1 =CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(49,57),RANDBETWEEN(65,90),RANDBETWEEN(97,122)))

copy right to J10.

in A2: = CONCATENATE(A1,B1,...,J1)

filippo

[simonwar]

Using the '=INT' and the Chars and uChars method, when the i refresh and therefore the volatile RAND changes, some entries change to '#VALUE!', and then chage back to a valid password if I refresh again?

I am refreshing by entering the relevant cell and dropping my cursor into the cell content window and pressing enter.

Any ideas why '#VALUE!' pops out of the equation at random?

Thanks, Simon.

[filo65]

Just realized that in the second "RANDBETWEEN" the range MUST be 48-57 ( 0 to 9 ) and not 49-57 ( 1 - 9 )

filippo

[Dave Hawley]

INT will return #Value if the nested function within returns text. This is why my example doesn't use INT when used with the INDEX function.

[simonwar]

Trying the RANDBETWEEN method, get the jist of the method, but the equation is throwing back the '#NAME?' error.

I tried simplifying the equation elements and see whats happening but couldn't break into the equation, always giving me an error?

Can you check the equation for me.

*** JUST NOTICED DO NOT HAVE RANDBETWEEN *** IS IT AN ADD-ON?
------------------------------------------

Here what your saying on the 'INT=' method. Is there a way of controlling results to stop getting 'text' returns ?


Appreciated, Simon.

[filo65]

simon,

I attach a spreadsheet. Could be I mistype something from german to english

filippo

[filo65]

could be check the AnalysisToolpack

filippo

[Dave Hawley]

Is there a way of controlling results to stop getting 'text' returns ?

Yes, like I have used it in the example.