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

 

[fredetchic]

Woaaa, perfect, works like a charm!

A bit more hard that I could have imagine

Thanks a lot, this is really helpful!

Fred

 

[paperbowser]

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.

This is great for creating alphanumeric strings with 50%letters and 50% numbers. I've modified this formula slightly for anyone wanting each allowed character to have the same probability of showing up, rather than 50% letters and 50% numerals.

=CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))

=CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))&CHAR(IF(RANDBETWEEN(1,36)<11,RANDBETWEEN(48,57),RANDBETWEEN(65,90)))


For the commenter who wanted to exclude "I" and "O" (presumably to prevent confusion with "1" and "0"), you can use this:

=CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))

=CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))&CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))&CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))&CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))&CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))&CHAR(CHOOSE(RANDBETWEEN(1,34),48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90))


OR you can make a list of all desired characters in a hidden column and use the following (example uses column R for the list of characters):

=INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)

=INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)&INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)&INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)&INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)&INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)&INDEX($R:$R,RANDBETWEEN(1,COUNTA($R:$R)),1)

 

[paperbowser]

For the second option, you can also avoid having to reference CHAR numbers when choosing your characters, but you'll be typing a lot of quotes:

=CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")

=CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")&

 

[Rick Rothstein]

For the second option, you can also avoid having to reference CHAR numbers when choosing your characters, but you'll be typing a lot of quotes:

=CHOOSE(RANDBETWEEN(1,34),0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F","G","H","J","K","L","M","N","P","Q","R","S","T","U","V","W","X","Y","Z")

A little easier to type perhaps...

=MID("0123456789ABCDEFGHJKLMNPQRSTUVWXYZ",RANDBETWEEN(1,34),1)

 

[flds]

Hi Scott,

I came across this code which I liked, I need help in modifying this code.
Is it possible to do so. If yes, I would like to see the generated output with mixed characters and colan punctuation between 2 characters (i.e. F2:3R:87:0T:58) . Note I tried generating a few, I could not see a 0 (zero) generated.
Your response would be appreciated.

Thanks
FLDS

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

 

[Scott Huish]

Try this:

Function GenerateCode()
Application.Volatile
Dim sections(1 To 5) As String
Dim i As Integer, j As Integer, s As String, t As String
Randomize
For i = 1 To 5
    For j = 1 To 2
        If Int(2 * Rnd) = 1 Then s = Chr(Int(10 * Rnd + 48)) Else s = Chr(Int(26 * Rnd + 65))
        t = t & s
    Next j
    sections(i) = t: t = ""
Next i
GenerateCode = Join(sections, ":")
End Function

I wrote this as a function so that you can insert the value in a cell or return the value to another function.

Excel 2010
	A
1	Y4:G3:A3:07:XZ
Sheet1

Cell Formulas
Range	Formula
A1	=GenerateCode()

 

[flds]

Thank you Scott for the function. Works great.

 

[lejoyeux3]

Hej Scott, I sent you a private mail, can you look into it?

 

[Scott Huish]

Hej Scott, I sent you a private mail, can you look into it?

You sent me a lot of code. But why not just post the part of the code that's not working here in this post, so that I am not the only one working on it.

 

[lejoyeux3]

I tried it but not working as I am only allowed to use 5000 characters. I also wanted t attach the excel doc so that you can see it for ourself but it is not possible t do so here. Do you haveany other solution for that?