Forum Discussion
Creating a Random Code Generator
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
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.
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
- Thank you so much for this and the explanation of how the formula works that will be a great help with future projects.