TransWikia.com

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?

12 Answers

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP