How to generate a random alphanumeric string with a formula in Excel (or Google Sheets or LibreOffice)

Question

I'm trying to generate a random 8 character alphanumeric string in Excel (or Google Sheets or Libreoffice, which both have the same challenge) using a formula. I'd like to get something like this:

6n1a3pax

I've tried various formulae including ones like this which generate the ASCII characters for individual random numbers between an upper and lower number:

=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) &CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))

However, they're lengthy, you have to repeat the RANDBETWEEN() function multiple times inside a formula, and you can't choose both "alpha" and "numeric" in the same RANDBETWEEN().

Is there any easy way to do this in Excel, Google Sheets or LibreOffice Calc? If a solution works in one and not in the others then great if you can mention which one(s).

(N.B. This is not a duplicate of questions about how to stop recalculation of randomisation functions in Excel)

Google Sheets, Microsoft Excel and LibreOffice Calc each have different feature sets. Choose one of them and edit tags appropriately. — doubleunary, Dec 05 '22 at 22:25
@doubleunary thanks - Excel, Google Sheets and Libreoffice all have the same challenge, there's no easy way to solve for them either. I'll edit though to make that clear. — Sam Critchley, Dec 05 '22 at 22:47
BTW someone marked this as a duplicate of https://stackoverflow.com/questions/66201364/how-to-stop-freeze-pause-volatile-rand-randbetween-randarray and closed the question. It's not a duplicate of that question, which asks how to "fix" cells displaying random(ised) output. This asks how to generate an alphanumeric string easily in a spreadsheet. — Sam Critchley, Dec 05 '22 at 22:48
@player0 - thanks. I see an answer which helps (e.g. "generate hex code 9 characters long"), but I didn't see it until now (and others won't, I believe) because the question is asking something different, namely how to stop reloading of volatile functions containing randomisation functions. I have a simple answer to my own question, but can't post it here because the question has been closed. If I post it as an answer to the other question it won't be relevant because it's not answering that question. — Sam Critchley, Dec 05 '22 at 23:28
Thanks @player0 - I like your answer too, so let's hope someone reopens the question. ;-) — Sam Critchley, Dec 05 '22 at 23:32
Of course I also tried this with chatGPT: "Generate 50 different 8-character hexadecimal strings." And it did. — Sam Critchley, Dec 06 '22 at 09:35
Google Sheets, Microsoft Excel and LibreOffice Calc each have different feature sets and will most likely require a different answer each. The [google-sheets] tag description explicitly states _"Do NOT use with [excel]"_ and the [excel] tag description explicitly states _"Do NOT use with other spreadsheet software like [google-sheets]."_ Choose one platform and edit tags appropriately. — doubleunary, Dec 07 '22 at 14:28

player0 · Answer 1 · 2022-12-05T23:35:28.600

4

in GS try:

=LAMBDA(x, x)(DEC2HEX(RANDBETWEEN(0, HEX2DEC("FFFFFFFF")), 8))

if that's not enough and you need

A-Z char 65-90
a-z char 97-122
0-9 char 48-58

=JOIN(, BYROW(SEQUENCE(8), LAMBDA(x, IF(COINFLIP(), IF(COINFLIP(), 
 CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(97, 122))), RANDBETWEEN(0, 9)))))

frozen:

=LAMBDA(x, x)(JOIN(, BYROW(SEQUENCE(8), LAMBDA(x, IF(COINFLIP(), IF(COINFLIP(), 
 CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(97, 122))), RANDBETWEEN(0, 9))))))

alternative (with better distribution):

=JOIN(, BYROW(SEQUENCE(8), LAMBDA(x, SINGLE(SORT(CHAR({
 SEQUENCE(10, 1, 48); 
 SEQUENCE(26, 1, 65); 
 SEQUENCE(26, 1, 97)}), 
 RANDARRAY(62, 1), )))))

or frozen:

=LAMBDA(x, x)(JOIN(, BYROW(SEQUENCE(8), LAMBDA(x, SINGLE(SORT(CHAR({
 SEQUENCE(10, 1, 48); 
 SEQUENCE(26, 1, 65); 
 SEQUENCE(26, 1, 97)}), 
 RANDARRAY(62, 1), ))))))

for more see: stackoverflow.com/questions/66201364

edited Dec 05 '22 at 23:35

answered Dec 05 '22 at 22:07

player0

124,011
12
67
124

What is the use of `COINFLIP()` ? – Mayukh Bhattacharya Dec 05 '22 at 22:31
1

@MayukhBhattacharya it's a coin toss 50% one side 50% the other side – player0 Dec 05 '22 at 22:33
Ah ok, so its available for every `Google Sheet` user right, its a new function, just saw for the first time. – Mayukh Bhattacharya Dec 05 '22 at 22:33
2

@MayukhBhattacharya yes its avail for all users and no, its not a new function. its a hidden (undocumented) function – player0 Dec 05 '22 at 22:35
Okay sounds good, its like the `DATEDIF()` in Excel. Great Share Learnt something new. – Mayukh Bhattacharya Dec 05 '22 at 22:37

JvdV · Answer 2 · 2022-12-07T07:51:42.327

4

LibreOffice Calc 7.x:

A non-volatile option for LibreOffice Calc 7.x is the use of the RANDBETWEEN.NV() function:

Formula in A1:

=CONCAT(IF({1,2,3,4,5,6,7,8},MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",RANDBETWEEN.NV(1,62),1),))

Note that using ROW(1:8) would still force recalculation when any value in rows 1-8 have been made (thus volatile):

=CONCAT(IF(ROW(1:8),MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",RANDBETWEEN.NV(1,62),1),))

Excel ms365:

Unfortunately there is, AFAIK, not a non-volatile Excel equivalent to this function. If volatility is not a problem, then try:

=CONCAT(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",RANDARRAY(8,,1,62,1),1))

edited Dec 07 '22 at 07:51

answered Dec 06 '22 at 09:31

JvdV

70,606
8
39
70

1

There are 62 characters in `A-Za-z0-9` rather than 64. – doubleunary Dec 07 '22 at 07:45
@doubleunary, that's it! Thanks for the razer sharp mind and eyes. Edited – JvdV Dec 07 '22 at 07:52

doubleunary · Answer 3 · 2022-12-07T07:53:54.513

Here's my take, for Google Sheets:

=lambda(_, 
  _ 
)(
  lambda( 
    numWords, wordLength, charRegex, ascii,
    lambda( 
      alphabet, 
      map( 
        sequence(numWords), 
        lambda(_, 
          concatenate( 
            map( 
              sequence(wordLength), 
              lambda(_, 
                mid(alphabet, randbetween(1, len(alphabet)), 1) 
              ) 
            ) 
          ) 
        ) 
      ) 
    )(concatenate(filter(ascii, regexmatch(ascii, charRegex)))) 
  )(10, 8, "[0-9a-zA-Z]", arrayformula(char(sequence(127)))) 
)

The formula will generate 10 passwords of 8 characters each from an alphabet that includes lower and upper case letters, and digits.

To choose which characters to include in the alphabet, replace [0-9a-zA-Z] with another regex like [0-9a-z!#$%&/] or [-!#$%&/\w]. Note that you may need to \escape any regex special characters there.

The pattern avoids the non-uniform distribution issues that plague some of the solutions presented in this thread. The ones that use coinflip() or isodd(rand()*N) will give results that overrepresent smaller sub alphabets like 0-9. The ones that use sort() will not repeat any chars in the result, which is not optimal.

Sam Critchley · Answer 4 · 2022-12-06T17:29:41.833

It's possible to do this in Excel using a combination of the following functions:

SEQUENCE() VSTACK() RANDARRAY() CHAR() INDEX() TEXTJOIN()

Unfortunately this doesn't work in LibreOffice (at the moment) as it does not have the SEQUENCE() function. It does not work in Google Sheets as the RANDARRAY() function only takes 2 parameters and the VSTACK() function does not exist, although you can use braces and a semicolon, e.g. {SEQUENCE(26,1,97,1);SEQUENCE(10,1,48,1)}.

Here's the formula you need:

Upper-case e.g "413BK5S0": =TEXTJOIN("",1,INDEX(CHAR(VSTACK(SEQUENCE(26,1,65,1),SEQUENCE(10,1,48,1))),RANDARRAY(8,1,1,36,TRUE)))

Lower-case e.g. "b8etbno8": =TEXTJOIN("",1,INDEX(CHAR(VSTACK(SEQUENCE(26,1,97,1),SEQUENCE(10,1,48,1))),RANDARRAY(8,1,1,36,TRUE)))

The following explanation for each function:

SEQUENCE() - a sequence of e.g. 26 numbers, in 1 column, starting at number 65, increasing by 1 each time (with the second incidence of the function being 10 numbers starting at 48)
VSTACK() - combine the 2 SEQUENCE() formulae into 1 array (sequence) of numbers
CHAR() - the ASCII character associated with a decimal ASCII number (where the decimal number is generated by the SEQUENCE() function) - see https://www.asciitable.com/
RANDARRAY() - an array of 8 random numbers, 1 column wide, minimum number 1, maximum 36
INDEX() - the value from each element within the sequence of characters, where each of 8 element numbers is provided by RANDARRAY()
TEXTJOIN() - join the values in an array together into one cell, with no separator and ignoring empty values

Thanks @player0 - you're right there. You can do `{SEQUENCE(26,1,97,1),SEQUENCE(10,1,48,1)}` but then you run into problems with `RANDARRAY()` only taking 2 parameters instead of Excel's 5. I'll update the answer to be clear it's Excel only. — Sam Critchley, Dec 06 '22 at 17:26

Martín · Answer 5 · 2022-12-05T22:24:43.850

What do you think of something like this?

=CONCATENATE(BYROW(SEQUENCE(8),LAMBDA(e,IF(ISODD(ROUNDUP(RAND()*10)),CHAR(RANDBETWEEN(65,90)),ROUNDDOWN(RAND()*10)))))

If you want to include lower case, you can do a similar logic:

=CONCATENATE(BYROW(SEQUENCE(8),LAMBDA(e,IF(ISODD(ROUNDUP(RAND()*10)),IF(ISODD(ROUNDUP(RAND()*10)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))),ROUNDDOWN(RAND()*10)))))

The logic is the next one: what I'm doing is with ISODD(ROUNDUP(RAND()*10) generating a random number between 1 and 10 and checking if it's odd. If it is, it generates a letter or else it generates a number. With CONCATENATE(BYROW(SEQUENCE(8)... I'm doing this 8 times and concatenating them. What I just added was a second "random and odd" time when it's time to generate a letter so you can have upper and lower case

I was reading that previous post to which this one was referred. My ISODD(ROUNDUP(RAND()*10) is basically replaced with COINFLIP(). Good to know that alternative! — Martín, Dec 05 '22 at 23:53

How to generate a random alphanumeric string with a formula in Excel (or Google Sheets or LibreOffice)

5 Answers5

alternative (with better distribution):