Creating a Random Code Generator

[Pete_Atkinson]

Hi,

I hope someone can help me with this.

I want to create random codes using certain letters i.e. no vowels and numbers.

I would also like to create random codes of varying lengths, sometimes 7 characters sometimes 9.

Thanks in advance for any help and advice that can be given.

[#2]

@Pete_Atkinson here it is a very bland calculator; since you didn't specified any other feature, I just used the first idea I had: try to check it and improve it, if necessary. It needs manual copypasting for each entry (TABLE sheet, column B, each row) to not lose the previous entry.

Hope it helps

[#3]

Thanks for your reply.
what I was ideally looking for was something similar to this which has been used before but i want to know how to make changes to the code length as this formula only creates 7 character codes.

The characters to be used are placed in cell A1 then the following formula is in cell A2 and dragged to create the number of codes required.
=LEFT( MID($A$1,RAND()*LEN($A$1)+1,1) &

[#4]

so a few questions:
a) what version of excel? ideally you have Excel 365 because dynamic arrays and the LET() function can help
b) looks like you cut off the formula but I'm guessing it repeats 7x?
c) can the same character be used multiple times in the code?
d) what frequency distribution for 7,8 or 9 character length or does it not matter (i.e. does it need to be = likely to have each?) or did you want that 'settable' (i.e. another cell is set to 7, 8 or 9 to determine the length)?

[#5]

Thank you for your reply.

I am using 365
The formula i posted is in it's entirety, and yes it creates a 7 character code from whatever is input in cell A1. I have attached a screen shot for clarification.
Yes the same characters can be used.
Creating codes of different lengths will depend on the application they will be used for, they will never be "mixed" so creating a file of 7 Characters and the next time might be 9 Characters. So a formula i can change would do the job or as you say having a "settable" reference cell would make life a little easier. Though i would like to understand the formula and how to alter how many characters it will create.

[#6 · Accepted solution]

@Pete_Atkinson so that can't be the 'whole' formula you posted as it isn't even valid.  There is probably additional lines you just don't see.  Regardless, here is a formula to use:

=CONCAT(MID($B$2,RANDARRAY($B$3,1,1,LEN($B$2),TRUE),1))

 B2 has the list of valid characters and B3 is how long you want the code to be.

See the attached sheet.

since you specifically asked to know how it works:

the RANDARRAY creates an array of B3 number of values between 1 and the total length of the list of valid characters (B2).

the MID() looks at the list of valid characters (B2) and pulls out the character located according to the RANDARRAY

and finally CONCAT puts it all together

[#7]

Thank you so much for this and the explanation of how the formula works that will be a great help with future projects.