Simulate a Loop in Google Sheet Cell to Generate Alphanumeric String

Question

I need to generate a random alphanumeric string as a reference in Google Sheets. I couldn't find a loop function outside of creating a new function in App Script.

I used some existing functions and the new "Named Function" in Google Sheets to make something. It seems to work fine, but I wonder if there is a better way to do it.

The breakdown of what I did to solve this is:

1. Get a random letter between A and Z.

=RANDBETWEEN( CODE("A"), CODE("Z") )

2. Get a random number between 0 and 9.

=RANDBETWEEN( CODE("0"), CODE("9") )

3. Create a random binary value. (We added the +1 because the receiving function isn't zero-based.)

=ROUND( RAND()+1 )

4. Put it together in a CHOOSE function wrapped in a CHAR function to return a random number or letter.

=CHAR( 
  CHOOSE( 
    ROUND( RAND()+1 ),
    RANDBETWEEN( CODE("0"), CODE("9") ),
    RANDBETWEEN( CODE("A"), CODE("Z") )
  )
)

That covers the mechanism to randomly generate a single character, but we need a string.

After some digging, I found that Google recently released the LAMBDA function, and combined with the MAP function, I thought maybe I solved it.

The MAP function takes an array, so I had to figure out how to create one dynamically, which landed me on the SEQUENCE function. Finally, I join the resulting array as a string.

=JOIN( "", 
  MAP( 
    SEQUENCE(1, 4), 
      LAMBDA(slot,
        CHAR( 
          CHOOSE(
            ROUND( RAND()+1 ),
            RANDBETWEEN( CODE("0"),CODE("9") ),
            RANDBETWEEN(CODE("A"),CODE("Z") )
          )
        )
      )
    )
  )

Once I got this, I made a named function with an argument where I could pass in the number of characters or "slots" like this:

=RANDALPHANUM(4)

Below is a link to a demo sheet, would love to see how much better this can be.

https://docs.google.com/spreadsheets/d/1ODLkUm1maj5_5rxhr_KlmB3vI_OE_-fIyesAevlaogc

Answer 1

random weighted distribution between A-Z & 0-9:

=JOIN(, BYROW(1:4, LAMBDA(x, SORTN(CHAR({
 RANDBETWEEN(48, 57); RANDBETWEEN(65, 90)}), 1,, 
 RANDARRAY(2, 1), 0))))

random non-weighted distribution between A-Z & 0-9:

=JOIN(, BYROW(1:4, LAMBDA(x, (SORTN(CHAR(
 {ROW(48:57); ROW(65:90)}), 1,, RANDARRAY(36), )))))

if you wish to freeze it see: https://stackoverflow.com/a/66201717/5632629