6

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?

  • If you use formulae to do that, then the unique string would be only unique for that moment. – Jacob Jan Tuinstra Feb 9 '15 at 8:36
  • Is a library allowed still....as not being an Add-on? – Jacob Jan Tuinstra Feb 9 '15 at 8:37
  • 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 Sep 27 '19 at 15:25
8

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.
  • 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 Feb 9 '15 at 16:22
  • 1
    This doesn't answer the question as it stands, only producing random lower and uppercase letters, no digits! – Cornelius Roemer Sep 27 '19 at 16:28
5

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

  • 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 Sep 27 '19 at 17:19
2
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)
  • Could you add a bit of explanation to the answer, please? – jonsca Sep 26 '18 at 23:51
  • This should be rated higher, not negative. But it doesn't provide lowercase. – Rocky Kev Sep 9 '19 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 Sep 27 '19 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)))
  • 1
    Please do not duplicate answers – user55949 Jun 13 '18 at 12:54
1

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.

  • 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 Sep 11 '19 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 Sep 27 '19 at 17:20
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)
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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