Generate random alphanumeric code

[mikeha_99]

Hello,
I would like to generate a random 5 character code. The random letters should be caps, and numbers between 0-9. An example might be: "F7B4K"

Ideally, at least one character in the result would be a letter.

I look forward to any and all suggestions.

Thank you,
Mike

 

[Oaktree]

Via formula, you could try:

=CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

to get a random alphanumeric character... or, 5 of those concatenated to get a 5 character one: =CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

Just press F9 to get a new value, or use a looping macro for your "... at least one character in the result would be a letter" restriction.

note: RANDBETWEEN requires the analysis toolpak.

 

[mikeha_99]

"or use a looping macro for your "... at least one character in the result would be a letter" restriction."

Thanks, I copied your formula, and it works, except I do get 5 numbers at times. I'm not sure what you meant with the line above?

Mike

 

[wddnld]

In VBA:

Sub createPW()
    Dim pw As String
    Dim i As Integer
 
    For i = 1 To 5
 
        If Int((2 * Rnd) + 1) = 1 Then
            pw = pw & Chr(Int((90 - 65 + 1) * Rnd + 65))
        Else
            pw = pw & Int((9 - 0 + 1) * Rnd + 0)
        End If
    Next i
 
    MsgBox pw
 
End Sub

This one uses the if statement to randomly select whether the character will be a number or letter.

This is a good reference on using Rnd
http://www.techonthenet.com/excel/formulas/rnd.php

 

[Scott Huish]

You'll want a Randomize in there:

Sub createPW()
Dim pw As String
Dim i As Integer
Randomize
For i = 1 To 5
    If Int((2 * Rnd) + 1) = 1 Then
        pw = pw & Chr(Int(26 * Rnd + 65))
    Else
        pw = pw & Int(10 * Rnd)
    End If
Next i
MsgBox pw
End Sub

 

[wddnld]

You're right, thank you.

 

[debbe]

Hi,

This code works great for generating random number - letter, could you tell me how to get the results to display in a cell rather than the pop-up msg box? I'm new to VBA so please dumb it down.

Thanks,
Debbe

You'll want a Randomize in there:

Sub createPW()
Dim pw As String
Dim i As Integer
Randomize
For i = 1 To 5
    If Int((2 * Rnd) + 1) = 1 Then
        pw = pw & Chr(Int(26 * Rnd + 65))
    Else
        pw = pw & Int(10 * Rnd)
    End If
Next i
MsgBox pw
End Sub

 

[Wessie]

Hi,

This code works great for generating random number - letter, could you tell me how to get the results to display in a cell rather than the pop-up msg box? I'm new to VBA so please dumb it down.

Thanks,
Debbe

Replace the second last line (Msgbox pw) with:

ActiveSheet.Range("C2").Formula = pw
C2 being any cell to you liking.

Cheers

 

[debbe]

This worked! So happy, thanks for your help. Much appreciated.

Replace the second last line (Msgbox pw) with:

ActiveSheet.Range("C2").Formula = pw
C2 being any cell to you liking.

Cheers

 

[Wessie]

Always a pleasure Debbe. Especially when I get thanked!