Forum Discussion
Creating a Random Code Generator
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.
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
6 Replies
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
- 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) &- 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)?
