{"id":1147,"date":"2024-07-30T00:41:30","date_gmt":"2024-07-29T23:41:30","guid":{"rendered":"https:\/\/wobable.com\/?p=1147"},"modified":"2024-07-30T00:41:30","modified_gmt":"2024-07-29T23:41:30","slug":"generating-pseudo-random-urns-in-excel","status":"publish","type":"post","link":"https:\/\/wobable.com\/?p=1147","title":{"rendered":"Generating (pseudo) random URNs in Excel"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">I had a need to generate a series of random unique references in excel &#8211; 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 &#8211; a series of five case sensitive either letters or numbers. For example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A7B9n<\/li>\n\n\n\n<li>PdZpI<\/li>\n\n\n\n<li>4W202<\/li>\n\n\n\n<li>m7yoz<\/li>\n\n\n\n<li>0q1e1<\/li>\n\n\n\n<li>R1JC2<\/li>\n\n\n\n<li>lv9MB<\/li>\n\n\n\n<li>JlIj2<\/li>\n\n\n\n<li>yQl7P<\/li>\n\n\n\n<li>AI1yJ<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">I was quite pleased with the solution I came up with:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &amp;\nCHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &amp;\nCHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &amp;\nCHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57))) &amp;\nCHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,90),RANDBETWEEN(97,122),RANDBETWEEN(48,57)))<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The way to understand the formula is that it generates five random letter\/numbers (case sensitive) &#8211; one for each line of the formula &#8211; and then joins them together (using the &#8220;&amp;&#8221; at the end of each line). I have split the formula over five lines (using ALT + RETURN) for legibility.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Each line of the formula starts by generating a number between 1 and 3 &#8211; RANDBETWEEN(1,3).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc\" data-type=\"link\" data-id=\"https:\/\/support.microsoft.com\/en-us\/office\/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc\">Choose function<\/a> 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 <a href=\"https:\/\/support.microsoft.com\/en-gb\/office\/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a\" data-type=\"link\" data-id=\"https:\/\/support.microsoft.com\/en-gb\/office\/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a\">Char() function<\/a> 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).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This gives one of over 916 million possible outcomes &#8211; 916132832 if we are going to be really picky!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">There are some caveats&#8230;<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Firstly, the excel formula recalculates every time you change something &#8211; so it may be easier to copy and &#8220;paste as values&#8221; your results, then  search for the matches.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Secondly, there is a small risk (especially if generating enormous volumes of these URNs) that you could (at least theoretically) <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">end up with duplicates. There are ways to remove duplicates &#8211; but you need to be aware of what is viewed as a duplicate in specific situations:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>My web server views filenames as case sensitive (so wibble.pdf and WIBBLE.pdf are different).<\/li>\n\n\n\n<li>My windows PC, however, views those two filenames as the same.<\/li>\n\n\n\n<li>Excel has an &#8220;EXACT&#8221; function which is case sensitive, however the functions that you would expect to be case sensitive (&#8220;<a href=\"https:\/\/support.microsoft.com\/en-gb\/office\/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e\" data-type=\"link\" data-id=\"https:\/\/support.microsoft.com\/en-gb\/office\/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e\">UNIQUE<\/a>&#8221; and <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a\" data-type=\"link\" data-id=\"https:\/\/support.microsoft.com\/en-us\/office\/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a\">MATCH<\/a>) are not case sensitive. There are ways of identifying unique case sensitive matches &#8211; either using a helper column or <a href=\"https:\/\/exceljet.net\/formulas\/index-and-match-case-sensitive\" data-type=\"link\" data-id=\"https:\/\/exceljet.net\/formulas\/index-and-match-case-sensitive\">the EXACT function<\/a> &#8211; but they are not immediately obvious. <\/li>\n\n\n\n<li><\/li>\n\n\n\n<li>It is also worth knowing that the equivalent of <a href=\"https:\/\/support.google.com\/docs\/answer\/10522653?hl=en&amp;sjid=6035706181391971159-EU\" data-type=\"link\" data-id=\"https:\/\/support.google.com\/docs\/answer\/10522653?hl=en&amp;sjid=6035706181391971159-EU\">&#8220;UNIQUE&#8221; in Google sheets<\/a> IS case sensitive!<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I had a need to generate a series of random unique references in excel &#8211; 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 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/wobable.com\/?p=1147\"> <span class=\"screen-reader-text\">Generating (pseudo) random URNs in Excel<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1],"tags":[],"class_list":["post-1147","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p2c0fT-iv","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/1147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1147"}],"version-history":[{"count":2,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/1147\/revisions"}],"predecessor-version":[{"id":1149,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/1147\/revisions\/1149"}],"wp:attachment":[{"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}