Web Applications Asked by GibboK on December 21, 2020
I need to create a random string of 8 characters for every row in Google Spreadsheet.
Basically string should contain letters and numbers upper and low case only, example:
LZJ2cuWT
Any idea how to do it without using Add-On?
You might try:
=CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),
CHAR(RANDBETWEEN(97,122)))&
CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),
CHAR(RANDBETWEEN(97,122)))
strung together with & three more times.
@Cornelius Roemer is correct so despite my misgivings about mixing 0 and O and 1 and l in a case like this, suggest as an alternative:
=offset($A$1,randbetween(0,61),)
concatenated as many times as the number of characters required in each set and copied wherever required.
However this depends upon a lookup table in ColumnA, which might be constructed so:
=char(row()+47)Correct answer by pnuts on December 21, 2020
Based on Majal, Yagisanatode, Cornelius(A digit is as likely as an upper case and lower case letter) and others.
Types of generated random numbers:
0- Numeric,
1- Alphanumeric upper case,
2- Alphanumeric lower case,
3- Alphanumeric upper & lower case,
4- Alphabet upper case,
5- Alphabet lower case,
6- Alphabet upper & lower case,
7- Alphanumeric upper case & first character alphabet upper case
8- Alphanumeric upper and lower case & first character alphabet upper case
9- Alphanumeric lower case & first character alphabet upper case
10-Alphanumeric with lower case & first character alphabet lower case
/**
* Return a random string
*
* @param {number} len character length.
* @param {number} num type of random number:
* 0-Numeric,
* 1-Alphanumeric upper case,
* 2-Alphanumeric lower case,
* 3-Alphanumeric upper & lower case,
* 4-Alphabet upper case,
* 5-Alphabet lower case,
* 6-Alphabet upper & lower case,
* 7-Alphanumeric upper case & first character alphabet upper case
* 8-Alphanumeric upper and lower case & first character alphabet upper case
* 9-Alphanumeric lower case & first character alphabet upper case
* 10-Alphanumeric with lower case & first character alphabet lower case
* @return string text with random characters.
* @customfunction
*/
function RANDOMGENERATOR(len, num) {
//Check if numbers
if(typeof len !== 'number' || typeof num !== 'number'){return text = "NaN"};
//var charString = "23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghkmnpqrstuvwxyz";
var charNumeric = "23456789"; // string 1
var charNumericLength = 8;
var charAlphaUpper = "ABCDEFGHJKLMNPQRSTUVWXYZ"; // string 2
var charAlphaUpperLength = 24;
var charAlphaLower = "abcdefghkmnpqrstuvwxyz"; // string 3
var charAlphaLowerLength = 22;
// Getting a random integer between two values, inclusive
// Math.floor(Math.random() * (max - min + 1)) + min;
// Math.floor(Math.random() * ( 3 - 1 + 1)) + 1;
var intMin = 0;
var intMax = 0;
var OneTWoThree = 0;
var text = "";
switch (num){
case 0:
//Numeric
intMin = 1;
intMax = 1;
break;
case 1:
//Alphanumeric with upper case
intMin = 1;
intMax = 2;
break;
case 2:
//Alphanumeric with lower case
intMin = 1;
intMax = 2;
charAlphaUpper = charAlphaLower;
charAlphaUpperLength = charAlphaLowerLength;
break;
case 3:
//Alphanumeric with upper and lower case
intMin = 1;
intMax = 3;
break;
case 4:
//Alphabet with upper case
intMin = 2;
intMax = 2;
break;
case 5:
//Alphabet with lower case
intMin = 3;
intMax = 3;
break;
case 6:
//Alphabet with upper and lower case
intMin = 2;
intMax = 3;
break;
case 7:
//Alphanumeric upper case & first character alphabet upper case
text += charAlphaUpper.charAt(Math.floor(Math.random() * charAlphaUpperLength));
len--;
intMin = 1;
intMax = 2;
break;
case 8:
//Alphanumeric with upper and lower case & first character alphabet upper case
text += charAlphaUpper.charAt(Math.floor(Math.random() * charAlphaUpperLength));
len--;
intMin = 1;
intMax = 3;
break;
case 9:
//Alphanumeric with lower case & first character alphabet upper case
text += charAlphaUpper.charAt(Math.floor(Math.random() * charAlphaUpperLength));
len--;
intMin = 1;
intMax = 3;
charAlphaUpper = charAlphaLower;
charAlphaUpperLength = charAlphaLowerLength;
break;
case 10:
//Alphanumeric with lower case & first character alphabet lower case
text += charAlphaLower.charAt(Math.floor(Math.random() * charAlphaLowerLength));
len--;
intMin = 1;
intMax = 3;
charAlphaUpper = charAlphaLower;
charAlphaUpperLength = charAlphaLowerLength;
break;
default:
//error reporting
return text = "Error: Type choice < 0 or > 6"
}
for (var i = 0; i < len; i++) {
// Getting a random integer between two values, inclusive
// Math.floor(Math.random() * (intMax - intMin + 1)) + intMin;
// Math.floor(Math.random() * ( 3 - 1 + 1)) + 1 ; // ==> Exemple
OneTWoThree = Math.floor(Math.random() * (intMax - intMin + 1)) + intMin;
switch (OneTWoThree){
case 1:
//Digit
text += charNumeric.charAt(Math.floor(Math.random() * charNumericLength));
break;
case 2:
//Alphabet character with upper case
text += charAlphaUpper.charAt(Math.floor(Math.random() * charAlphaUpperLength));
break;
default:
//Alphabet character with lower case
text += charAlphaLower.charAt(Math.floor(Math.random() * charAlphaLowerLength));
}
}
return text;
}
Answered by Gopala Dasa - HDG on December 21, 2020
Which is looks like the most statistically sound so far by the limits of pseudo-random generation. :-)
=CONCATENATE(ARRAYFORMULA(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", CEILING(RANDARRAY(1, 8), 1 / 62) * 62, 1)))
Say you have cell A1 containing your key character set, i.e. for the OP: ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789, then this would work:
=CONCATENATE(ARRAYFORMULA(MID($A$1, CEILING(RANDARRAY(1, 25), 1 / LEN($A$1)) * LEN($A$1), 1)))
=CONCATENATE(ARRAYFORMULA(MID($A$1, CEILING(RANDARRAY(1, RANDBETWEEN(25, 40)), 1 / LEN($A$1)) * LEN($A$1), 1)))
Apps Script, using javascript, may work. Just note that custom Sheet functions need to be deterministic. Here is a sample of a custom function from https://yagisanatode.com/2018/08/23/google-sheets-random-alphabetic-random-alphanumeric-and-random-alphanumeric-character-custom-functions/:
function RANDALPHA(len, num) {
var text = "";
//Check if numbers
if(typeof len !== 'number' || typeof num !== 'number'){return text = "NaN"};
var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";
var charStringRange
switch (num){
case 0:
//Alphabet with upper and lower case
charStringRange = charString.substr(0,52);
break;
case 1:
//Alphanumeric
charStringRange = charString.substr(0,62);
break;
case 2:
//Alphanumeric + characters
charStringRange = charString;
break;
default:
//error reporting
return text = "Error: Type choice > 2"
}
//
for (var i = 0; i < len; i++)
text += charStringRange.charAt(Math.floor(Math.random() * charStringRange.length));
return text;
}
Usage would be =RANDALPHA(character length, type of random number)
Where types of random numbers would be:
You can edit the JS to your liking. :-) Thanks to the original author!
Answered by Majal on December 21, 2020
For strings, numbers, social security numbers (fake, of course), zip codes, etc you can try our add-on: https://gsuite.google.com/marketplace/app/random_data_generator/626703404158
Just click on a range of cells to put the data in and insert. Easy.
Answered by Lovely API on December 21, 2020
I created the following formula that omits the following characters due to difficulties for users to differential various characters. This is especially useful for default password generator that's printed out for users to type into system.
Characters omitted:
Two random characters generator:
=CHOOSE(RANDBETWEEN(1,8),CHAR(RANDBETWEEN(50,57)),CHAR(RANDBETWEEN(65,72)),CHAR(RANDBETWEEN(74,78)),CHAR(RANDBETWEEN(80,90)),CHAR(RANDBETWEEN(97,104)),CHAR(107),CHAR(RANDBETWEEN(109,110)),CHAR(RANDBETWEEN(112,122))) &
CHOOSE(RANDBETWEEN(1,8),CHAR(RANDBETWEEN(50,57)),CHAR(RANDBETWEEN(65,72)),CHAR(RANDBETWEEN(74,78)),CHAR(RANDBETWEEN(80,90)),CHAR(RANDBETWEEN(97,104)),CHAR(107),CHAR(RANDBETWEEN(109,110)),CHAR(RANDBETWEEN(112,122)))
To concatenate more characters, just append with "&", followed by the above formula.
Answered by Fong Kah Chun on December 21, 2020
In fact, there isn't one definite definition of random string - there are different definitions used in the answers with different character distributions. The first solution is one that I've created that hasn't mentioned before and might be what most people expect when they talk about a random string with a number of characters
In probabilities:
p('0')=p('1')=...=p('A')=...p('z')=1/62
but:
p(0 or 1 ... or 9])=10/62
p([a-z])=p([A-Z])=26/62
All characters used must be equally likely, so a '1' must occur as often as a 'Z' or a 'd', with a probability of 1 in 62 (A..Z+a..z+0..9). This was inspired by Chris River's neat variable length solution - but with RANDARRAY instead of SEQUENCE to suit the more complicated circumstances.
=CONCATENATE(ARRAYFORMULA(CHOOSE(CEILING(2.615384615*RANDARRAY(1,8)+0.3846153846),
CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))))
To get digits to come up with the right probability (10/62) the choose function needs to be fed with integers 1 [p=10/62], 2 [p=26/62], 3 [p=26/62], this is achieved by applying ceiling with rand and an appropriate offset.
In probabilities:
p(0 or 1 ... or 9])=1/3
p([a-z])=p([A-Z])=1/3
p('0')=1/30 != p('A')=1/78
This is the random string type you get by using the previously suggested methods with RANDBETWEEN(1,3) instead of the more complicated ceiling and rand functions.
=CONCATENATE(ARRAYFORMULA(CHOOSE(RANDBETWEEN(SEQUENCE(1, 8, 1, 0),3),
CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))))
In probabilities:
p([0-9])=p([A-F])=1/16
p([a-z])=p([G-Z])=0 (G-Z & a-z never occur)
This has a neat solution, but only provides a limited character set (see SnosRap75's answer). Changing the 2 to 8 yields a length=8 string.
=DEC2HEX(RANDBETWEEN(0, 4294967295), 2)
Answered by Cornelius Roemer on December 21, 2020
To build on earlier answers, this formula makes it easy to change the length of the generated password:
=CONCATENATE(ARRAYFORMULA(CHOOSE(RANDBETWEEN(SEQUENCE(1, 8, 1, 0),3),
CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))))
The 8 within SEQUENCE defines the length of the string generated. Sequence and arrayformula here allow us to generate a single random character spread across 8 columns, which we then concatenate into a single cell.
This could be expanded to generate a random string of random length, like this:
=CONCATENATE(ARRAYFORMULA(CHOOSE(RANDBETWEEN(SEQUENCE(1, RANDBETWEEN(8, 16), 1, 0),3),
CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))))
Answered by Chris River on December 21, 2020
If you are concerned about the differences between 1 l I (one, lima, capital india), then
=MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)
might be helpful, where A1 contains your list of characters to choose from. In my case that was: "ABCDEFGHJKLMPRTUXZ23456789"; for the answer, it would also contain lower case characters, e.g. "ABCDEFGHJKLMPRTUXZ23456789abcdefghijklmprtux"
You can easily concatenate that, in case of 8 characters:
=MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)&MID($A$1,RANDBETWEEN(1,LEN($A$1)),1)
Answered by Herbert Van-Vliet on December 21, 2020
I can't comment or upvote apparently, but SnosRap75 is correct.
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)
This will give you what you are looking for and will work in Excel or Sheets. If you change the "8" you change the number of characters generated.
Answered by Daniel Tanguay on December 21, 2020
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)
Answered by SnosRap75 on December 21, 2020
for a 10 character string comprising 0-9 A-Z a-z :
=CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
&CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
.
Before I get slated, The reason I posted this as a "duplicate" answer is because I came across this question looking for a simple copy-paste solution, and I couldn't post this as a comment due to being over 600(?) characters...
Answered by user3616725 on December 21, 2020
You can try, if you want to have randomness from 0 ~ 9, A ~ Z (capitalized):
=CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(48,57)),
CHAR(RANDBETWEEN(65,90)))&
CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(48,57)),
CHAR(RANDBETWEEN(65,90)))
Answered by Wayne Chiu on December 21, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP