Alphanumeric random number?

L

L. Howard Kittle

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
 
M

Michelle B

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?
 
B

bplumhoff

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 = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
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
 
Top