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)

Prae

Google Sheets, Microsoft Excel and LibreOffice Calc each have different feature sets. Choose one of them and edit tags appropriately. – Haler

Are you allowed to use macros? – Kehr

No macros allowed unfortunately. ;-) – Prae

@Haler 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. – Prae

BTW someone marked this as a duplicate of #66201864 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. – Prae

the answer can be also found there... – Bayles

@Bayles - 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. – Prae

added alternative – Bayles

Thanks @Bayles - I like your answer too, so let's hope someone reopens the question. ;-) – Prae

I can reopen it for you to post the answer... – Bayles

Thanks @Bayles - I could only vote for it to be reopened – Prae

Of course I also tried this with chatGPT: "Generate 50 different 8-character hexadecimal strings." And it did. 😬 – Prae

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

Answer 1

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

Bayles

What is the use of COINFLIP() ? – Logic

@MayukhBhattacharya it's a coin toss 50% one side 50% the other side – Bayles

Ah ok, so its available for every Google Sheet user right, its a new function, just saw for the first time. – Logic

@MayukhBhattacharya yes its avail for all users and no, its not a new function. its a hidden (undocumented) function – Bayles

Okay sounds good, its like the DATEDIF() in Excel. Great Share 👍🏼 Learnt something new. – Logic

Answer 2

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

Noni

There are 62 characters in A-Za-z0-9 rather than 64. – Haler

@doubleunary, that's it! Thanks for the razer sharp mind and eyes. Edited – Noni

Answer 3

Here's my take, for Google Sheets:

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

The formula will generate 10 IDs of 8 characters each from an alphabet that includes lower and upper case letters, and digits. Each ID is guaranteed to be unique.

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 in the pattern, as in [\+\?\^\$\{\}\(\)\|\[\]\\].

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.

Haler

Answer 4

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

Prae

VSTACK() is not a valid function in GS – Bayles

Thanks @Bayles - 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. – Prae

Answer 5

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

Nat

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! – Disputable

Answer 6

Here's a LAMBDA function for Excel 365 that has (1) parameter to set the output string length. You can reuse it in Name Manager. This is based on Sam Critchley's Answer:

=LAMBDA(string_length, LET( codesLower, SEQUENCE(26, 1, CODE("a"), 1), codesUpper, SEQUENCE(26, 1, CODE("A"), 1), codesNumeral, SEQUENCE(10, 1, 48, 1), chars, CHAR(VSTACK(codesLower, codesUpper, codesNumeral)), return1, TEXTJOIN( "", 1, INDEX(chars, RANDARRAY(string_length, 1, 1, ROWS(chars), TRUE)) ), return1 ) )

Here's the same thing, but formatted by Excel Labs for easier understanding:

=LAMBDA(string_length,
    LET(
        codesLower, SEQUENCE(26, 1, CODE("a"), 1),
        codesUpper, SEQUENCE(26, 1, CODE("A"), 1),
        codesNumeral, SEQUENCE(10, 1, 48, 1),
        chars, CHAR(VSTACK(codesLower, codesUpper, codesNumeral)),
        return1, TEXTJOIN(
            "",
            1,
            INDEX(chars, RANDARRAY(string_length, 1, 1, ROWS(chars), TRUE))
        ),
        return1
    )
)

Notes

1. To invoke this LAMBDA function immediately, without name manager, just put (string length) behind the formula in a cell.

E.g.,

=LAMBDA(string_length, LET( codesLower, SEQUENCE(26, 1, CODE("a"), 1), codesUpper, SEQUENCE(26, 1, CODE("A"), 1), codesNumeral, SEQUENCE(10, 1, 48, 1), chars, CHAR(VSTACK(codesLower, codesUpper, codesNumeral)), return1, TEXTJOIN( "", 1, INDEX(chars, RANDARRAY(string_length, 1, 1, ROWS(chars), TRUE)) ), return1 ) )(8)

does exactly what you asked for.

2. This is a volatile function. You can replace the formula to keep it static.
3. This formula includes capital letters, too. You can change which sets of characters are included by changing the arguments of the VSTACK(). You could also expand on my work and make another parameter of the main LAMBDA functions that enables setting which arrays of characters are used.

Drat