-
-
Save msubel/9c70951a20efe5c72195 to your computer and use it in GitHub Desktop.
=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))) |
In Portuguese Brasil
=MINÚSCULA(CONCATENAR(DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;8));8);"-";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;4));4);"-";"4";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;3));3);"-";DECAHEX(ALEATÓRIOENTRE(8;11));DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;3));3);"-";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;8));8);DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;4));4)))
In Hungarian:
=KISBETŰ(ÖSSZEFŰZ(DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;8));8);"-";DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;4));4);"-";"4";DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;3));3);"-";DEC.HEX(VÉLETLEN.KÖZÖTT(8;11));DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;3));3);"-";DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;8));8);DEC.HEX(VÉLETLEN.KÖZÖTT(0;HATVÁNY(16;4));4)))
I just need to say thank you too! Lifesaver
In Turkish:
=KÜÇÜKHARF(BİRLEŞTİR(DEC2HEX(RASTGELEARADA(0;KUVVET(16;8));8);"-";DEC2HEX(RASTGELEARADA(0;KUVVET(16;4));4);"-";"4";DEC2HEX(RASTGELEARADA(0;KUVVET(16;3));3);"-";DEC2HEX(RASTGELEARADA(8;11));DEC2HEX(RASTGELEARADA(0;KUVVET(16;3));3);"-";DEC2HEX(RASTGELEARADA(0;KUVVET(16;8));8);DEC2HEX(RASTGELEARADA(0;KUVVET(16;4));4)))
It generates duplicates for large numbers of UUIDS (14 500 of them) in osx, I ran it 5 or 6 times and there were around 8 duplicates each time.
Also it is recalculated everytime
Spanish Version:
=MINUSC(CONCAT(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)))
For within VBA:
Target.Value = _
LCase(WorksheetFunction.Concat( _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 8)), 8), _
"-", _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 4)), 4), _
"-", "4", _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 3)), 3), _
"-", _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(8, 11)), _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 3)), 3), _
"-", _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 8)), 8), _
WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, WorksheetFunction.Power(16, 4)), 4) _
))
Polish version with ";"
=LITERY.MAŁE(ZŁĄCZ.TEKSTY(DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;8));8);"-";DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;4));4);"-";"4";DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;3));3);"-";DZIES.NA.SZESN(LOS.ZAKR(8;11));DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;3));3);"-";DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;8));8);DZIES.NA.SZESN(LOS.ZAKR(0;POTĘGA(16;4));4)))
in Portuguese:
=MINÚSCULAS(CONCATENAR(DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;8));8);"-";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;4));4);"-";"4";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;3));3);"-";DECAHEX(ALEATÓRIOENTRE(8;11));DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;3));3);"-";DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;8));8);DECAHEX(ALEATÓRIOENTRE(0;POTÊNCIA(16;4));4)))
Svenska/Swedish one-liner:
=GEMENER(SAMMANFOGA(DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;8));8);"-";DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;4));4);"-";"4";DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;3));3);"-";DEC.TILL.HEX(SLUMP.MELLAN(8;11));DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;3));3);"-";DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;8));8);DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;4));4)))
Indented for readability:
=GEMENER(
SAMMANFOGA(
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;8));8);
"-";
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;4));4);
"-";
"4";
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;3));3);
"-";
DEC.TILL.HEX(SLUMP.MELLAN(8;11));
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;3));3);
"-";
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;8));8);
DEC.TILL.HEX(SLUMP.MELLAN(0;UPPHÖJT.TILL(16;4));4)
)
)
Other languages translated on this page:
https://en.excel-translator.de/translator/
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.
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)))
in Russian
=СТРОЧН(СЦЕПИТЬ(ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;8));8);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;4));4);"-";"4";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;3));3);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(8;11));ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;3));3);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;8));8);ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;СТЕПЕНЬ(16;4));4)))