Login/Register
How to generate a random alphanumeric string with a formula in Excel (or Google Sheets or LibreOffice)
Asked Answered
excelgoogle-sheetsrandomexcel-formulalibreoffice-calc
P

6

5

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 answered 5/12, 2022 at 21:57 Comment(13)
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
B
6

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

enter image description here

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

enter image description here

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 answered 5/12, 2022 at 22:7 Comment(5)
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
N
4

LibreOffice Calc 7.x:

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

enter image description here

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 answered 6/12, 2022 at 9:31 Comment(2)
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
H
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 answered 6/12, 2022 at 14:55 Comment(0)
P
2

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 answered 6/12, 2022 at 9:27 Comment(2)
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
N
1

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

enter image description here

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

enter image description here

Nat answered 5/12, 2022 at 22:6 Comment(1)
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
D
1

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.

  1. This is a volatile function. You can replace the formula to keep it static.
  2. 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 answered 27/6 at 16:23 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.