there is currently no RANDBETWEEN function, I also requested the need to the dev team, maybe we get something like that soon.
But there is a RANDOM() function, that gives you a random float number and you can workaround a lot of stuff with it. While I was able to randomize a 1-letter-3-numbers solution, I was not able to get 3-letters-3-numbers, here is how and why:
1. three numbers
This part can be solves with a simple
Random() returns a number like 0.1348134524. With the parameter “false” the number will not update everytime you make a change in the doc.
The formula Right() returns just a piece of it, beginning from the right.
All together it should give you a random number between 001 and 999.
2. one letter
This is more of a challenge, since with our trick we can always only randomize between 0-9, 01-99, 001-999 … but for the character formula und need to go for 65-90.
What we can do is creating a helper table with two column. One column represents the numbers from 1-99, the other one has the numbers 65-90, repeated until all rows are filled.
You can now make a table lookup with a random number between 1 and 99 and you will get back a number between 65 and 90. You can fill this number in the character formula and get a random character as you needed.
3. why not three letters?
You can line up formulas with the formula “concatenate”. So at first I thought “ok, let’s just concatenate the letter formula three times and we will get three random letters”.
But there is one problem: Random only gives your ONE random number per formula, even if you use random multiple times in it:
So my “solution” does work for one letter, but not for three, because you would end up with this:
Three times the same letter…
Any one has a solution to create three random letters (without making a table with every possible three letter combination)?