Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
8th August 2003
Location
UK
Posts
61
Usergroup
Free questions

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.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Digital Products Password Generator ...

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.
Last edited by Dave Hawley; May 15th, 2007 at 11:20. Reason: Fix #VALUE! error

3. ## Re: Create Alphanumeric Passwords

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

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
8th August 2003
Location
UK
Posts
61
Usergroup
Free questions

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.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Create Alphanumeric Passwords

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

filippo

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Create Alphanumeric Passwords

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.

7. Member
Join Date
8th August 2003
Location
UK
Posts
61
Usergroup
Free questions

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.
Last edited by simonwar; May 14th, 2007 at 19:45.

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Create Alphanumeric Passwords

simon,

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

filippo

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Create Alphanumeric Passwords

could be check the AnalysisToolpack

filippo

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: Create Alphanumeric Passwords

Is there a way of controlling results to stop getting 'text' returns ?
Yes, like I have used it in the example.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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