Hi,
I’d like to add a column that will assign a random 3 letter 3 number combination ex. “EMU357”, that will be assigned as reference numbers for a list of clients.
In Excel I would use this formula
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(001,999)
Does anyone know if there’s a similar formula available in Coda that I can use?
Hey @Linda_Stridh_Creed,
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:
This part can be solves with a simple
=right(random(false),3)
formula.
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.
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.
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)?
Thanks! I wasn’t aware of the false for random! This is great!!!
I must say I’ve been very much inspired for some time by your ideas and contribution to the community, so firstly I want to thank you Daniel, many of your posts were a huge help.
I tried to develop on your idea and after tinkering for the last few evenings I came up with some solution that works pretty well, I think.
So right to the point:
• I managed to fit the solution inside one formula (no lookup tables).
• The solution is based on the ability of defining by you the desired length and char/number pattern just by specifying an alphanumeric string which results in a desired random combination.
• Pretty long combinations of letters/numbers are possible (but the randomness starts to become not technically random at some point, but firstly - no one will notice, secondarily - no one cares).
• This solution/formula can be used in several ways, for example with hardcoded pattern string inside the formula or using another column for this purpose, all of which involves a few small changes inside the formula that must be made.
• The formula itself looks quite ridiculous so some explanations are needed.
• For example FormulaMap seems to be used here in a particularly strange way, but it is an attempt to ensure the correct order of execution in relation to the potential issue as described here: Visual Position of the row in table
I put a few examples in this coda doc below - as describing this is quite heavy so I’ll spare myself this mental gymnastics, at least for now:
Doc: Random number/character generator
Example 1:
Example 2:
Example 3:
Example 4:
@Kris_Murawski wow, this is incredilble!
This is by far the most complex formula that I’ve seen in Coda 
I saved a local copy with your name in it. This is super useful! Thank you so much
Cool, @tomavatars. It looks like you made a hash generator!
If you want a random number between 1 and 3 just multiply 3 by Random() and you’ll get something between. You can use Round() if you want an integer.
Can you please give us the URL of the doc? I don’t think coda docs in google dirve are valid anymore:
Dear @BODHI_SCHNEIDER,
This is a shared copy from the original
Just let me know if you are able to get a copy of it, I am using my phone and not sure it’s going well
As of Coda recent version (July 2024) elegant formula solution:
Sequence(start,end).RandomSample(quantity,false)
example use:
Sequence(1,9999).RandomSample(12,false)
results into a list of 12 random numbers between 1 to 9999, allow same random number appear multiple times false.
mixed use cases such as sorted alphabet like this example:
Sequence(65,90).RandomSample(8).ForEach(CurrentValue.Character()).Sort()
where the CurrentValue.Character() converts the number 65-90 into Uppercase letters. other character range can be determined by looking up the characters unicode value.
This current improve solution makes use maximize the recently added formula RandomSample() and thoughtfully similar random generation formulas.
Oh that’s pretty cool @chrisquim!
I didn’t really know about the Character() so I looked into a bit more, and also found that Coda could handle a few more rows than I previously thought ^^
