1

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.

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

player0
  • 124,011
  • 12
  • 67
  • 124
David Geere
  • 404
  • 5
  • 10
  • Hi David, Please what is the intended output you need? generate a random alphanumeric string as a reference like [A-AZ]&[1-1000] ? – Osm Oct 15 '22 at 06:58
  • whats your question again? – player0 Oct 15 '22 at 08:18
  • On this site, [we maintain a strict separation between questions and answers](/tour). If you've found the solution to your own problem, that's great! Please [answer your own question](/help/self-answer) in the space for an answer below. – TheMaster Oct 15 '22 at 09:00
  • 1
    If you want to improve a educating existing solution, [codereview.se] maybe appropriate. – TheMaster Oct 15 '22 at 09:03
  • @player0 it was "I need to generate a random alphanumeric string as a reference in Google Sheets." So a string like say "7G3DE" – David Geere Oct 15 '22 at 21:40

1 Answers1

2

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

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

player0
  • 124,011
  • 12
  • 67
  • 124