Random number/character generator

#1

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?

1 Like
#2

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:

1. three numbers

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.

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

4 Likes
#3

Thanks! I wasn’t aware of the false for random! This is great!!!

#4

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:

4 Likes
#5

@Kris_Murawski wow, this is incredilble!

This is by far the most complex formula that I’ve seen in Coda :smiley:

I saved a local copy with your name in it. This is super useful! Thank you so much

1 Like