Home > Mobile >  Simulate a Loop in Google Sheet Cell to Generate Alphanumeric String
Simulate a Loop in Google Sheet Cell to Generate Alphanumeric String

Time:10-16

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") )
  1. Get a random number between 0 and 9.
=RANDBETWEEN( CODE("0"), CODE("9") )
  1. Create a random binary value. (We added the 1 because the receiving function isn't zero-based.)
=ROUND( RAND() 1 )
  1. 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.

1

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

2

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

  • Related