# 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

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 = "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: • 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

### How to retrieve the lost password for my email account?

1  Asked on February 18, 2021 by user509653

### Using the Wayback Machine without loading scrolling images on its front page

1  Asked on February 18, 2021

### Unable to find and locate the “Third-party data sources card”

2  Asked on February 17, 2021 by ab-abrams

### Remove admins promoted by Deleted Account, Telegram

1  Asked on February 17, 2021

### How do I set up my Gmail profile picture so others can see it, and not just me?

1  Asked on February 16, 2021 by geri

### Nesting if statements

1  Asked on February 15, 2021 by user264021

### How to locate a specific slide using a certain layout in Google Slides?

1  Asked on February 9, 2021 by jvc

### Why do birthdays show up in my calendar?

1  Asked on February 6, 2021

### Is there a Google Play link that automatically starts playing music?

1  Asked on February 5, 2021 by charles-clayton

### Google Sheets: How do I protect a sheet without locking its sheet name?

0  Asked on February 4, 2021

### In Google Drive, is there a way to search files located in a particular folder?

15  Asked on February 4, 2021 by jon-schneider

### Only display email in To field when sending a Gmail message

1  Asked on February 3, 2021 by nam-g-vu

### Switch between different Tab´s

1  Asked on February 2, 2021

### How to remove “image results” or “video results”

0  Asked on February 1, 2021

### A formula for importing specific data into another sheet

2  Asked on January 31, 2021 by tedpenner

### How do I delete all my activity history with Microsoft?

1  Asked on January 31, 2021 by tamschi

### What web apps are available to dynamically forward email according to a schedule?

0  Asked on January 26, 2021 by tom-wright

### Can I import data from web store directly to Sheet?

1  Asked on January 25, 2021 by gary-d

### Issue in importing citations to EndNote via Google Scholar

0  Asked on January 25, 2021