How to generate a random alphanumeric string with a formula in Excel (or Google Sheets or LibreOffic-CodePudding

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)

CodePudding user response：

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:

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