How to generate 16 digit unique random alpha numeric numbers?

[matevijay]

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.

[stanleydgromjr]

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?

[ARGK]

Hi
Try this formula:

=RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(1000,9999)&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10000,99999)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

Good luck.
Tony

[6StringJazzer]

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.

[shg]

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)

[6StringJazzer]

shg, delightful! Thanks for pointing that out.

[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. 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.

Please Login or Register  to view this content.

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)

[matevijay]

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?

Examples of unique random codes are "pHWaIwBLy1w3sGT7", "G32YNyQ7p7kb28NJ", "9DEPRHDk7gT1Zw15", "1243ZB38MyTV3ubw"

[matevijay]

Examples of unique random codes are "pHWaIwBLy1w3sGT7", "G32YNyQ7p7kb28NJ", "9DEPRHDk7gT1Zw15", "1243ZB38MyTV3ubw"

[matevijay]

Thanks Tony

[stanleydgromjr]

tigeravatar,

Nicely done - one for my archives.

[sonny8488]

Excelent tiger, I was looking for something like this too

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

[SPTech]

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.

Please Login or Register  to view this content.

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)

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.

[6StringJazzer]

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.

[6StringJazzer]

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.

[ivandgreat]

Thanks for this code..

Any help to add dashes in between strings on the 9th, 14th, 19th, and 24th characters for the 32 characters..

br,

[PatMaloney]

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

Really late to the party here, but is this because the number of unique combinations has been exceeded? If you had 3 characters (0,1&2) in your list and wanted a random 2-character strings the maximum possible unique values is 3*3=9 (00,01,02,10,11,12,20,21,22) So asking it to create 10 unique values would fail.

Patrick.

[wyoh]

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.

I know this is an old thread, but very helpful! Tigeravatar, the code worked wonderfully for my sheet. The only thing that I would like to be able to do, is allow a user to customize how many codes are generated without going into the Developer tools to change the # of Dim arrUnqAlphaNums in the VB code?. Is this a possibility?

[JeteMc]

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.

[wyoh]

My apologies!