Random hexadecimal number generator

[Leslie]

My goal is to be able to create on demand a single random hexadecimal number
26 characters long. Is there a formula combining worksheet functions which
will do that job?

I tried to combine RANDBETWEEN and DEC2HEX, but couldn't make them produce a
single number of the required length, no doubt for reasons which are beyond
my understanding of these matters.

 

[keepITcool]

=DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))
&DEC2HEX(INT(RAND()*15))&DEC2HEX(INT(RAND()*15))


could use named function to shorten this a bit


nah.. lots easier if you resort to vba:

Function RandHex()
Dim s$, i%
For i = 0 To 25
Randomize
s = s & Hex(Rnd * 15)
Next
RandHex = s
End Function


yo!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[JE McGimpsey]

Your understanding might be better if you read Help for Dec2Hex, which
states that the function operates on integers in the range
-549,755,813,888 to 549,755,813,887, which in turn produces only a 10
character hex string.

For 26 hex chars, you'll have to write your own function. You can't
really use XL's double precision functions, since a 26 character hex
sample space is 0 to 16^26, or 2.02824E+31 decimal, but XL's precision
is only about 15 decimal digits, not 31.

 

[JE McGimpsey]

Note that these functions will exclude about 18% of the possible
"random" numbers.

Change the 15s to 16s to correct the worksheet functions, and change

s = s & Hex(Rnd * 15)

to

s = s & Hex(Int(Rnd*16))

 

[keepITcool]

Now I'm confused..

my code generates 0 to F
which of the 16 numbers from 0 to 15 am I missing?
PLEASE explain.


keepITcool


< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

ouch...

done some testing...
done some thinking...
done some reading...

you are (ofcourse) correct...

and I will NEVER make this mistake again
Thanks!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Leslie]

My thanks to both of those who replied to my query. As JE McGimpsey said, I
should have paid more attention to the reference in the help file for
DEC2HEX to the maximum permissible number with which DEC2HEX can deal.

Concentrating on that limitation made me do the following:

1. put in A1: =RANDBETWEEN(100000000000,549755813887)
2. repeat in B1 and C1
3.put in A2: =DEC2HEX(A1)
4. repeat in B2 and C2 (with appropriate change of cell reference)
5. put in A4: =CONCATENATE(A2,B2,C2)
6. put in A6: =MID(A4,3,26)

That seems to generate a random hexadecimal number of 26 characters which
changes significantly every time I press the F9
function key.

If there's some reason why what I've just mentioned is unsatisfactory, I'd
be grateful if anyone would tell me. (Incidentally, I'm not worried by the
fact that the "number" isn't really a number, but is text.)

Thanks again.

 

[JE McGimpsey]

A truly random number shouldn't *necessarily* "change significantly"
each iteration.

Also, by excluding values from 0 to 100000000000, you're excluding a lot
of potential random numbers.

Better to use a random generator for each hex digit, as keepITcool
illustrated.