How to generate 16 digit unique random alpha numeric numbers?

SOLVED I want to generate 16 digit unique random alpha numeric numbers in excel. How to go about it? I require 10ooo numbers which should not get repeated.

matevijay,

Welcome to the Excel Forum.

Can you give us some examples of what the 16 digit unique random alpha numeric numbers should look like?

1. Do you want upper and lower case alpha characters?

2. Do you want the space character?

3. Do you want numbers 0 thru 9?

The attached workbook [matevijay=generate random 16-character strings.xlsx] is set up to generate 10,000 random strings of 16 characters (not digits) each composed of digits 0-9 and letters A-Z (uppercase only). It is capable of producing any such combination. It uses a named formula called RANC (Random AlphaNumeric Character) that generates one random alphanumeric character. The named formula uses RANDBETWEEN so the list will regenerate every time you open the file or hit F9. To save it, copy Column A and do a Paste Values to another location.

This method does not guarantee that every value will be unique. It is highly unlikely but possible to have duplicates. By definition, if you require that every value is unique then you are not simulating randomness. I implemented another version [matevijay=generate random 16-character strings with dupe check.xlsx] that checks for (but does not prevent) duplicates but for 10,000 entries it takes a while to recalculate.

If you absolutely require uniqueness then I think a macro is required.

6SJ,

I like your approach, but it preferentially chooses numbers (one of 10 symbols half the time) versus letters (26 symbols the other half of the time).

An alternative for RANC:

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

EDIT: Or

=MID("0123456789ABCDEFGHIJKLMONPQRSTUVWXYZ", RANDBETWEEN(1,36), 1)

matevijay,

Just in case you'd like a macro solution, this one will produce 10000 unique alphanumeric entries very quickly and put them in column A of the active sheet starting in A1. I did use shg's method for getting a random alphanumeric character. I did my best to make it customizable. For a different number of results, change the (1 to 10000) in the Dim arrUnqAlphaNums line to be (1 to x) where x is the desired number of results. To change which characters can be used for your alphanumeric strings, change the strCharacters to only include the characters desired.

Sub tgr()
    
    Const strCharacters As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
    Dim cllAlphaNums As Collection
    Dim arrUnqAlphaNums(1 To 10000) As String
    Dim varElement As Variant
    Dim strAlphaNum As String
    Dim AlphaNumIndex As Long
    Dim lUbound As Long
    Dim lNumChars As Long
    Dim i As Long
    
    Set cllAlphaNums = New Collection
    lUbound = UBound(arrUnqAlphaNums)
    lNumChars = Len(strCharacters)
    
    On Error Resume Next
    Do
        strAlphaNum = vbNullString
        For i = 1 To 16
            strAlphaNum = strAlphaNum & Mid(strCharacters, Int(Rnd() * lNumChars) + 1, 1)
        Next i
        cllAlphaNums.Add strAlphaNum, strAlphaNum
    Loop While cllAlphaNums.Count < lUbound
    On Error GoTo 0
    
    For Each varElement In cllAlphaNums
        AlphaNumIndex = AlphaNumIndex + 1
        arrUnqAlphaNums(AlphaNumIndex) = varElement
    Next varElement
    
    Range("A1").Resize(lUbound).Value = Application.Transpose(arrUnqAlphaNums)
    
    Set cllAlphaNums = Nothing
    Erase arrUnqAlphaNums
    
End Sub

And just in case...
How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one tgr)

Originally Posted by brettwilson

This macro worked for me, but the minute i try and increase the amount of codes that i need above 60000 it fails can someone help ??

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Originally Posted by tigeravatar

matevijay,

Just in case you'd like a macro solution, this one will produce 10000 unique alphanumeric entries very quickly and put them in column A of the active sheet starting in A1.

Tiger, is there anyway you can modify this coding to fill in a specific range of cells, specifically B6-R225 instead of the single row?

Thanks.

Originally Posted by SPTech

Tiger, is there anyway you can modify this coding to fill in a specific range of cells, specifically B6-R225 instead of the single row?

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Originally Posted by chubbycheeks

Hi,

Is it possible to have this in lower and upper case?

Thanks,
Chubs

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

tigeravatar,

Nicely done - one for my archives.

Administrative Note:

wyoh, Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Let us know if you have any questions.