# How to generate a random string?

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:

1. In A1 copied down to Row75: =char(row()+47)
2. Ctrl+C then Ctrl+Shift+V
3. Delete Rows11-17 then Rows 37-42.

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

### Direct answer to OP:

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


# Built-in Functions

Say you have cell A1 containing your key character set, i.e. for the OP: ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789, then this would work:

### Fixed length (e.g. 25)

=CONCATENATE(ARRAYFORMULA(MID($A$1, CEILING(RANDARRAY(1, 25), 1 / LEN($A$1)) * LEN($A$1), 1)))


### Variable length (e.g. 25 to 40)

=CONCATENATE(ARRAYFORMULA(MID($A$1, CEILING(RANDARRAY(1, RANDBETWEEN(25, 40)), 1 / LEN($A$1)) * LEN($A$1), 1)))


# Custom Functions

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 = "[email protected]#$%^&*()<>-=_+:;"; 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: • 1: Alphabetic • 2: Alphanumeric • 3: Alphanumeric + Characters 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: • 0 • 1 • 'I' • 'O' • 'i' • 'j' • 'l' • 'o' 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 ### Maximum entropy (every string equally likely) [probably what people normally mean] 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. ### Every character type equally likely: p([0-9])=p([a-z])=p([A-Z])=1/3 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)))))  ### Characters 0-9, A-F equally likely (G-Z & a-z never occur) 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

## Related Questions

### Checking if a value is within one of many possible ranges

0  Asked on January 24, 2021 by rietty

### Google Sheets: Lookup Value that falls after a Timestamp

0  Asked on January 23, 2021 by user261687

### How do I search a string, and return all values?

1  Asked on January 23, 2021 by y-lin

### Twitter trending list not correct

1  Asked on January 23, 2021 by beth202

### Date-Sensitive Interpolation of Missing Data in Google Sheets

1  Asked on January 18, 2021 by wil

### Emails backed up on external hard-drive, how do I transfer those emails into a new gmail account?

1  Asked on January 17, 2021 by joel-zorrilla

### Can I send support questions to Google via email?

1  Asked on January 17, 2021 by mafu

### How to filter Google Sheets by multiple contains using AND

2  Asked on January 17, 2021

### Turn off all comments in Facebook Group as admin

0  Asked on January 16, 2021 by roser-veronica

### Can Google Apps admins monitor the chat history of users?

1  Asked on January 15, 2021 by ananth

### Find or conditional format a column of phone numbers NOT in desired format

3  Asked on January 13, 2021 by genny

### Email forwarding to Gmail and SPF

1  Asked on January 13, 2021 by daunpunk

### Facebook feature: Help people start a conversation with your Page

1  Asked on January 10, 2021 by diane

### Why is my Twitter Page in Arabic?

1  Asked on January 10, 2021 by mariana-beaumont

### How to sort files in folder by date created?

3  Asked on January 8, 2021 by user1306322

### How do I associate a storefront order number with a shopping cart transaction on AliExpress?

0  Asked on January 7, 2021 by zhro

### In Google Ads, a broad match keyword of 3 words doesn’t match a search with a 4th word inserted

0  Asked on January 6, 2021

### How to subtract two columns in Google Sheets query?

1  Asked on January 6, 2021

### Why I am getting this error message from Gmail?

0  Asked on January 4, 2021 by abu-bakar-saadat

### Sort a column by name with query between tabs

1  Asked on January 3, 2021 by richard-novy

### Ask a Question

Get help from others!