Generate unique random alphanumeric characters and strings

If you play lottery games of lotto type or want to get a list of some unique random numbers, letters or expressions, Excel is very helpful in generating them. There are many ways to accomplish that. Here are just couple of them.

To create a list of unique random numbers using an array formula

First, format a sellected cell, e.g. A1, as Text. Enter a range of numbers from which to draw, in this format: 1-50 . Next, enter in cell A2 the following array formula:

=SMALL(IF(COUNTIF(C$1:C1,ROW($1:$50))<>1,ROW($1:$50)),1+INT(RAND()*(RIGHT(C$1,LEN(C$1)-FIND("-",C$1))-LEFT(C$1,FIND("-",C$1)-1)+1-ROW()+ROW(C$2))))

To enter it correctly, hold CTRL+SHIFT and press ENTER key on your keyboard. If you need to generate random unique numbers from a different range, you have to replace the numbers (1 and 50) in both A1 and A2 cells.

Now you need to copy cell A2 and paste it to as many rows as many random numbers you are expecting to obtain (up to 50 in the example).

To create a list of unique random numbers using UDF (user defined function)

• Press ALT+F11 on your keyboard to open up the VB Editor.
• If no Module is available within your Project, then right-click within the VBA Project Explorer and select Insert, then choose Module from the menu.
• Enter the following code in the module:

Function GetUnique(Lo As Integer, Hi As Integer, Nr As Integer) As String
'Generates x unique random numbers between any 2 numbers you specify
'e.g. =GetUnique(1,50,8) produces 8 unique random numbers between 1 and 50
    Dim iArr As Variant
    Dim i As Integer
    Dim r As Integer
    Dim temp As Integer
    Application.Volatile
    ReDim iArr(Lo To Hi)
    For i = Lo To Hi
        iArr(i) = i
    Next i
    For i = Hi To Lo + 1 Step -1
        r = Int(Rnd() * (i - Lo + 1)) + Lo
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    For i = Lo To Lo + Nr - 1
        If i = Lo Then GetUnique = GetUnique & "" & iArr(i)
        If i > 1 Then GetUnique = GetUnique & " * " & iArr(i)
    Next i
    GetUnique = Trim(GetUnique)
End Function

• Use the function in any selected cell by entering it in the format shown above on the comment line.

To create a random letter of alphabet (eithe Upper- or Lowercase)

Use this formula:  

=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))) 

It generates a random letter from a to z and A to Z characters.

To create a string of random letters,

like in this example:

 

 

 

 

you need to use "&" character to add the letters together:

=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))

This specific formula generates a string of three random letters. If you drag the cell fill handle over to some range you want to cover with this formula, you will get the range filled with random 3-letter strings. The formula can be quite easily modified to get unique random strings including any and many (more than three) alphanumeric characters.