View original (French)

Generate random Excel password

Solved
Anonyme -  
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   -
Hello,

In Excel:
Column A: Last Name
Column B: First Name
Column C: @mail

But for column D, I would like to generate a randomly complex password, like: qFH9rL6
All automatically when entering the 3 pieces of information in columns A, B, and C!
What formula should I use in column D?

Thank you

Best regards,

Anonymous
Configuration: Windows XP Firefox 3.5.3

5 answers

Answer 1 / 5
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Hello,
It's not easy to "fiddle" with a formula that gives you a random result. Through VBA, a macro would do that without any problem. However, by tinkering with the ALEA() formula in Excel, you can get a result:
=MID(A1,ROUNDUP(RAND()*62,0),1)
In A1, you place the uppercase alphabet, the digits from 0 to 9, and the lowercase alphabet, all together without spaces:
ABCDE...0123....abcde...
in B1 the formula:
=MID(A1,ROUNDUP(RAND()*62,0),1)
To obtain 7 random characters:
You need to place the same formula 7 times with & between:
=MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)&MID(A1,ROUNDUP(RAND()*62,0),1)
To stretch your formula don't forget to place $ signs like this $A$1 instead of each A1.
The problem with this formula is that it is "volatile". So with each new entry in your spreadsheet, the result changes.
You can work around this problem by doing: copy / Paste special "values" either manually or through a very simple-to-use automatic macro:
In a VBA module, you insert this code:
Sub Macro1()
ActiveCell.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
You click on the cell where you just indicated the formula and press ALT + F8, choose Macro1, and execute.
This macro copies / pastes special into the cell to the right of the active cell...
--
Best regards,
-- What is worth doing is worth doing well --
3