Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Create Alphanumeric Passwords

  1. #1
    Join Date
    8th August 2003
    Location
    UK
    Posts
    61

    Create Alphanumeric Passwords

    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. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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. #3
    Join Date
    2nd December 2004
    Location
    Frankfurt, D
    Posts
    383

    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. #4
    Join Date
    8th August 2003
    Location
    UK
    Posts
    61

    Re: Create Alphanumeric Passwords

    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. #5
    Join Date
    2nd December 2004
    Location
    Frankfurt, D
    Posts
    383

    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. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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. #7
    Join Date
    8th August 2003
    Location
    UK
    Posts
    61

    Re: Create Alphanumeric Passwords

    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. #8
    Join Date
    2nd December 2004
    Location
    Frankfurt, D
    Posts
    383

    Re: Create Alphanumeric Passwords

    simon,

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

    filippo
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    2nd December 2004
    Location
    Frankfurt, D
    Posts
    383

    Re: Create Alphanumeric Passwords

    could be check the AnalysisToolpack

    filippo

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: November 11th, 2003, 02:33
  2. How to create an alphanumeric series in Excel?
    By rdaubin in forum EXCEL HELP
    Replies: 6
    Last Post: February 16th, 2003, 20:16
  3. How di I create two passwords for one file
    By Sdixon in forum EXCEL HELP
    Replies: 2
    Last Post: February 4th, 2003, 05:35

Bookmarks

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