Created
July 14, 2015 12:37
-
-
Save msubel/9c70951a20efe5c72195 to your computer and use it in GitHub Desktop.
An Excel Fromula to generate a UUID v4
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0;POWER(16;8));8);"-";DEC2HEX(RANDBETWEEN(0;POWER(16;4));4);"-";"4";DEC2HEX(RANDBETWEEN(0;POWER(16;3));3);"-";DEC2HEX(RANDBETWEEN(8;11));DEC2HEX(RANDBETWEEN(0;POWER(16;3));3);"-";DEC2HEX(RANDBETWEEN(0;POWER(16;8));8);DEC2HEX(RANDBETWEEN(0;POWER(16;4));4))) |
Just one question... The GUID's get refreshed evertime I do something to the column or near the column that contain the formula. Is there a way to just generate it once and fix the generated GUID's?
Regards, Arie
I set one cell in excel to be my UUID generator using the formula and naming the range, then I use VBA to refer to the named range when I want to commit the value to a stored location using something like:
For Each rRecord In rNewRecords
rRecord.Offset(0, -1).Value = [r_uuid].Value
[r_uuid].Calculate
Next rRecord
Where r_uuid is a named range in the workbook.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I get #NUM! errors one in every few thousand calculations of this formula.
Here is why:
The fix is to add -1 after each use of the POWER() function in the formula:
=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0;POWER(16;8)-1);8);"-";DEC2HEX(RANDBETWEEN(0;POWER(16;4)-1);4);"-";"4";DEC2HEX(RANDBETWEEN(0;POWER(16;3)-1);3);"-";DEC2HEX(RANDBETWEEN(8;11));DEC2HEX(RANDBETWEEN(0;POWER(16;3)-1);3);"-";DEC2HEX(RANDBETWEEN(0;POWER(16;8)-1);8);DEC2HEX(RANDBETWEEN(0;POWER(16;4)-1);4)))