Is it possible to create alphanumeric random numbers ?

One play ..

Put in any cell, copy it down / across as desired:
=CHAR(randbetween(65,90))&randbetween(1000,9999)

Above will return random strings comprising of a capital alpha between A -
Z, concatenated with a random 4 digit number between 1,000 and 9999, eg:

N9484
L7773
F3785
Y1198
C5555
etc

Pressing F9 re-generates. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michelle B" wrote:
Is it possible to create alphanumeric random numbers ?

Thanks Max
Would never have got there by myself !!

"Max" wrote:

One play ..

Put in any cell, copy it down / across as desired:
=CHAR(randbetween(65,90))&randbetween(1000,9999)

Above will return random strings comprising of a capital alpha between A -
Z, concatenated with a random 4 digit number between 1,000 and 9999, eg:

N9484
L7773
F3785
Y1198
C5555
etc

Pressing F9 re-generates. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michelle B" wrote:
Is it possible to create alphanumeric random numbers ?

You're welcome, Michelle !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michelle B" wrote in message
...
Thanks Max
Would never have got there by myself !!

Hi Michelle B,

Using this formula (which should all be on one line) go to Insert Name
Define Names in workbook XX Refers to Enter the long formula OK

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789
abcdefghijklmnopqrstuvwxyz",
1+INT(62*RAND()),1)


On your worksheet use this formula to generate your alpha-numeric numbers.

=XX&XX returns a 2 character a/n number
=XX&XX&XX returns 3 character a/n number
etc.

Adjust to the length of the alpha-numeric number you want by addin "&XX's"
to the worksheet formula.

HTH
Regards,
Howard


"Michelle B" wrote in message
...
Is it possible to create alphanumeric random numbers ?

Thanks - have tried this & it works perfectly, but is there any way to
guarantee a combination of 2 alpha & 2 numeric in random positions every time?


"L. Howard Kittle" wrote:

Hi Michelle B,

Using this formula (which should all be on one line) go to Insert Name
Define Names in workbook XX Refers to Enter the long formula OK

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789
abcdefghijklmnopqrstuvwxyz",
1+INT(62*RAND()),1)


On your worksheet use this formula to generate your alpha-numeric numbers.

=XX&XX returns a 2 character a/n number
=XX&XX&XX returns 3 character a/n number
etc.

Adjust to the length of the alpha-numeric number you want by addin "&XX's"
to the worksheet formula.

HTH
Regards,
Howard


"Michelle B" wrote in message
...
Is it possible to create alphanumeric random numbers ?

Hello Michele,

Try this UDF:
Function ranpw(l As Long, ByVal ca As Long, ByVal cn As Long) As String
'random alpha-numeric password with length l and a minimum of
'ca alpha characters and cn numerical characters
Dim s As String, sr As String
Dim r As Long, i As Long

Application.Volatile
s = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklm nopqrstuvwxyz"
If l < 1 Or ca < 0 Or cn < 0 Or l < ca + cn Then
ranpw = CVErr(xlErrNum)
Exit Function
End If

For i = 1 To l
If l - i + 1 = ca Then
r = 52 * Rnd() + 11
ElseIf l - i + 1 = cn Then
r = 10 * Rnd() + 1
Else
r = 62 * Rnd() + 1
End If
If r 10 Then
If ca 0 Then ca = ca - 1
Else
If cn 0 Then cn = cn - 1
End If
sr = sr & Mid(s, r, 1)
Next i

ranpw = sr

End Function

Press ALT + F11, insert a module, and copy this function into the
module.

If you need a password of length 5 with at least 2 alpha and 2
numerical characters:
=ranpw(5,2,2)

HTH,
Bernd