My Random Password Generator

[NeoMoses]

I've seen a few questions asking about random password generation, so I decided to try my hand at it. The entire sheet wouldn't fit, but I think this will get my point across. It uses no VBA, so a simple F9 will recalculate the sheet and give you all new passwords. Enjoy!

password generator.xls
	B	C	D	E	F	G	H	I	J
1	only lower case	pbqg	4 Digit			Character Table
2		uatfu	5 Digit			Upper	Lower	Number	Special
3		gfkqua	6 Digit			B	s	5	*
4		xsobifa	7 Digit			H	q	9	*
5		ddsqguis	8 Digit			Q	x	2	$
6		rgpbquorq	9 Digit			B	q	0	"
7		pwqqwpoisi	10 Digit			S	u	1	(
8	Date	5/6/2005 7:47				O	q	2	#
9	Lower + Upper	oqik	4 Digit			B	k	3	%
10		bpTTQ	5 Digit			H	q	3	&
11		IBuHtD	6 Digit			I	m	8	*
12		AhsDIgS	7 Digit			T	f	4	!
13		BgfhJTQf	8 Digit			Z	f	9	!
14		pBkHiBiEI	9 Digit			A	i	0	"
15		qZdpMbaxgE	10 Digit			P	r	9	#
Password Generator 1.0

 

[erik.van.geit]

Hello, NeoMoses,

it's fine you want to share your thoughts!
but it's a bit confusing since you didn't follow the recomandations to post your sheet: that's why you get &nbsp and also &amp
EDIT your post : erase htmlcode
run again the procedure
choose the "view code" button
select all
copy and paste here

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.

kind regards,
Erik

 

[NeoMoses]

Ok, it should be displaying properly now.

Basically, there's 2 things going on. First, generate a table of random characters that you can use to choose from using =CHAR(RANDBETWEEN())

I chose 4 columns, UpperCase, LowerCase, Numbers, and Special Characters. I arbitrarily chose 30 rows long.

Once you've generated your table of random characters that fit your password rules, randomly select characters from this table using:
=INDEX(G3:J33,RANDBETWEEN(1,30),2)

Now, all you have to do to make an N-character random password is string together N of the above lines with the ampersand (&) like so:

=INDEX(G3:J33,RANDBETWEEN(1,30),2)&INDEX(G3:J33,RANDBETWEEN(1,30),2)...

As you may be able to tell, I've set this sheet up to have 4 different types of passwords: lowercase only, lower+upper, lower+upper+numbers, and finally all characters. This way, if a certain site does not allow special characters, you can still quickly and easily choose a password.

I put the NOW() function in there for screencaps. It may be useful in the future for remembering passwords.

 

[erik.van.geit]

thank you !

kind regards,
Erik

 

[santeria]

which version?

Does the above formula need an addin or a specific version of excel ?
Does not seem to work in Office XP.

thank you !

kind regards,
Erik

 

[erik.van.geit]

Hi, Santeria,

I'm glad you came back to the board !
We didn't see you for a long time ?

yes, it needs some more: you need analysis toolpak
but you can easily use rand() instead
randbetween(1,30)
INT(RAND()*30+1)

general formula see helpfiles
Int((max - min + 1) * Rnd + min)


Erik

 

[santeria]

mMMM.
I am trying to get it for people who don't/can't have the analysis toolpak.
Can RAND be construed so as to have a 6 or 7 character password ?
Not real clear if this is doable.

Yup, finally back to doing some more excel. Mostly tech support right now, but its phone based ( no fun), hopefully I can get to the university and do support there.

Hi, Santeria,

I'm glad you came back to the board !
We didn't see you for a long time ?

yes, it needs some more: you need analysis toolpak
but you can easily use rand() instead
randbetween(1,30)
INT(RAND()*30+1)

general formula see helpfiles
Int((max - min + 1) * Rnd + min)


Erik

 

[erik.van.geit]

Santeria,

here you get my inspiration of the last hour
I think this is wonderful

B2: =CHAR(INT(RAND()*(57-48+1)+48))
B3: =CHAR(INT(RAND()*(90-65+1)+65))
B4: =CHAR(INT(RAND()*(122-97+1)+97))
B6: =INT(RAND()*2)*32
B7: =CHAR(INT(RAND()*(90-65+1)+65)+INT(RAND()*2)*32)
B9: =IF(RAND()>26/36,CHAR(INT(RAND()*(57-48+1)+48)),CHAR(INT(RAND()*(90-65+1)+65)))
B10:=IF(RAND()>52/62,CHAR(INT(RAND()*(57-48+1)+48)),CHAR(INT(RAND()*(90-65+1)+65)+INT(RAND()*2)*32))
column C has same formulas with & in between
= formula & formula & formula
for instance
C2: =CHAR(INT(RAND()*(57-48+1)+48))&CHAR(INT(RAND()*(57-48+1)+48))&CHAR(INT(RAND()*(57-48+1)+48))

randomcode.xls
	A	B	C	D	E
1	what	1 sign	3 signs	code	comment
2	number	0	541	48 to 57
3	uppercasecode	A	GSR	65 to 90
4	lowercasecode	r	zek	97 to 122
5
6	0 or 32	0			32 = 97 - 65
7	ucase & lcase	T	axX
8
9	ucase & number	Z	ST9		36 = 26 + 10
10	uc & lc & number	l	vE6		62 = 26x2 + 10
Blad3

kind regards,
Erik

 

[erik.van.geit]

sorry, I completely forgot to substitue the 122 - 97 + 1 ... by the result
here are the formulas without those "calculations

=CHAR(INT(RAND()*10+48))
=CHAR(INT(RAND()*26+65))
=CHAR(INT(RAND()*26+97))

=INT(RAND()*2)*32
=CHAR(INT(RAND()*26+65)+INT(RAND()*2)*32)

=IF(RAND()>26/36,CHAR(INT(RAND()*10+48)),CHAR(INT(RAND()*26+65)))
=IF(RAND()>52/62,CHAR(INT(RAND()*10+48)),CHAR(INT(RAND()*26+65)+INT(RAND()*2)*32))

Erik

 

[MTBer]

this site is useful

http://www.tushar-mehta.com/excel/tips/rand_alphanum.html

alternatively if you want a random password generator email me at oldschoolbug@aol.com, and i'll send you one i wrote in vb6