Instantly share code, notes, and snippets.

# msubel/uuid_v4_excel_formula.txt

Created July 14, 2015 12:37
Show Gist options
• 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)))

### pbubar commented Nov 28, 2023

I get #NUM! errors one in every few thousand calculations of this formula.

Here is why:

• POWER(16;4) returns 65536.
• RANDBETWEEN is inclusive of the 'bottom' and 'top' values.
• DEC2HEX(65536;4) throws the NUM error as its needs 5 characters; the highest value DEC2HEX can return in 4 characters is 65535
• So the POWER() function is giving a 'top' bound to the RANDBETWEEN that is exactly 1 too high.

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

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.

### CodedSpirit-dev commented Jul 2, 2024

For Spanish-MX Excel

`=MINUSC(CONCATENAR(DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,8)),8),"-",DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,4)),4),"-","4",DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,3)),3),"-",DEC.A.HEX(ALEATORIO.ENTRE(8,11)),DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,3)),3),"-",DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,8)),8),DEC.A.HEX(ALEATORIO.ENTRE(0,POTENCIA(16,4)),4)))`