15

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?

Improve this question
3
  • If you use formulae to do that, then the unique string would be only unique for that moment.
    – Jacob Jan
    Commented Feb 9, 2015 at 8:36
  • Is a library allowed still....as not being an Add-on?
    – Jacob Jan
    Commented Feb 9, 2015 at 8:37
  • 1
    What sort of randomness do you want? Should a digit be as likely as an upper case and lower case letter? Or should digits be equally likely as all letters? Do you want all letters to be equally likely among each other? The answers in this question produce a variety of different random strings without acknowledging that they produce different results!
    – Cornelius Roemer
    Commented Sep 27, 2019 at 15:25

13 Answers 13

Reset to default
14

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.
Improve this answer
2
  • 2
    Is a cell using this in one row supposed to change when you paste the formula into a new row?
    – Eight Days of Malaise
    Commented Feb 9, 2015 at 16:22
  • 1
    This doesn't answer the question as it stands, only producing random lower and uppercase letters, no digits!
    – Cornelius Roemer
    Commented Sep 27, 2019 at 16:28
12 
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)
Improve this answer
3
  • Could you add a bit of explanation to the answer, please?
    – jonsca
    Commented Sep 26, 2018 at 23:51
  • This should be rated higher, not negative. But it doesn't provide lowercase.
    – Rocky Kev
    Commented Sep 9, 2019 at 19:56
  • This string only contains digits and upper case characters from A to F, G-Z and a-z never occur in this random string.
    – Cornelius Roemer
    Commented Sep 27, 2019 at 17:20
10

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

Improve this answer
1
  • Beware that with this formula a digit is 2.6 times more likely to occur than a letter, not necessarily what one might expect from a random string.
    – Cornelius Roemer
    Commented Sep 27, 2019 at 17:19
4

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)
Improve this answer
2

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.

Improve this answer
2
  • If you want to change the length, you'll need to change the range too. So this is easier (change both 8s): =DEC2HEX(RANDBETWEEN(0, 16^8), 8)
    – tjmcewan
    Commented Sep 11, 2019 at 7:08
  • This string only contains digits and upper case characters from A to F, G-Z and a-z never occur in this random string.
    – Cornelius Roemer
    Commented Sep 27, 2019 at 17:20
1

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)))
Improve this answer
0
1

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)))))
Improve this answer
0

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)
Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
0

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!

Improve this answer
0

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;
}
Improve this answer
0

Here's another option:

  1. This formula generates an 8 char/number string using random numbers and upper/lowercase letters
  2. I left out the numbers 0 and 1, based on earlier comments, but they can be added in by replacing the condition "CHAR(RANDBETWEEN(50,57))" with "CHAR(RANDBETWEEN(48,57))"
  3. You can choose the length of the generated string by updating the "8" in "SEQUENCE(1,8,1,0)" to the number of characters you need

=ARRAYFORMULA(JOIN("",CHOOSE(SEQUENCE(1,8,1,0),CHOOSE(RANDBETWEEN(1,3),CHAR(RANDBETWEEN(50,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))))))

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.