Generating (pseudo) random URNs in Excel

I had a need to generate a series of random unique references in excel – I was creating some filenames, and needed to ensure that they were not easily guessable for someone else getting a link to one of the files. The sort of thing I was looking for was the random endings you get for Bit.ly urls or surveymonkey links – a series of five case sensitive either letters or numbers. For example:

  • A7B9n
  • PdZpI
  • 4W202
  • m7yoz
  • 0q1e1
  • R1JC2
  • lv9MB
  • JlIj2
  • yQl7P
  • AI1yJ

I was quite pleased with the solution I came up with:

=CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &
CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &
CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &
CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &
CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57)))

The way to understand the formula is that it generates five random letter/numbers (case sensitive) – one for each line of the formula – and then joins them together (using the “&” at the end of each line). I have split the formula over five lines (using ALT + RETURN) for legibility.

Each line of the formula starts by generating a number between 1 and 3 – RANDBETWEEN(1,3).

The Choose function then uses that number to decide whether the character in that position of the result will be an uppercase letter, a lowercase letter or a digit. The Char() function returns a character corresponding to the ANSI code for a random number generated in the relevant range (uppercase = ANSI 65 to 90, lowercase = ANSI 97 to 122, digits 0 to 9 = ANSI 48 to 57).

This gives one of over 916 million possible outcomes – 916132832 if we are going to be really picky!

There are some caveats…

Firstly, the excel formula recalculates every time you change something – so it may be easier to copy and “paste as values” your results, then search for the matches.

Secondly, there is a small risk (especially if generating enormous volumes of these URNs) that you could (at least theoretically)

end up with duplicates. There are ways to remove duplicates – but you need to be aware of what is viewed as a duplicate in specific situations:

  • My web server views filenames as case sensitive (so wibble.pdf and WIBBLE.pdf are different).
  • My windows PC, however, views those two filenames as the same.
  • Excel has an “EXACT” function which is case sensitive, however the functions that you would expect to be case sensitive (“UNIQUE” and MATCH) are not case sensitive. There are ways of identifying unique case sensitive matches – either using a helper column or the EXACT function – but they are not immediately obvious.
  • It is also worth knowing that the equivalent of “UNIQUE” in Google sheets IS case sensitive!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.