How to create a GUID in Excel?

L

15

79

I need a function to add a GUID to cells in excel. I found this previous question on stackoverflow, but it is not working. It suggests the following function:

=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,6553‌5),4),"-"
    ,DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151‌),4),"-"
    ,DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

I have been unable to get the concatenate method to work, and so tried it using the "&" figure to concatenate anyway. That seemed to work, but then I get an inexplicable error on the second DEX2HEX block: DEC2HEX(RANDBETWEEN(0,6553‌5),4). The Excel formula evaluator says it's invalid, but I can't seem to figure out why at all. Any ideas?

Lucho answered 20/2, 2013 at 21:26 Comment(11)

Why not just use the VBA code supplied in the same question? – Stortz 20/2, 2013 at 21:30

Which version of Excel are you using? AFAIK RANDBETWEEN was only introduced in 2007. If you use 2003, replace it with INT(RAND()*65535) – Crumbly 20/2, 2013 at 21:32

@PeterAlbert but then I get an inexplicable error on the second DEX2HEX block - assuming that 1st block is ok) – Disinherit 20/2, 2013 at 21:33

@PeterL.: Not sure I understand your comment... :-( – Crumbly 20/2, 2013 at 21:45

@PeterAlbert I suppose that means 1st block did NOT get errors, or simply RANDBETWEEN works fine. – Disinherit 20/2, 2013 at 21:50

@PeterL.: Ahh, I see. Stupid me, should read the question better next time... :-) – Crumbly 20/2, 2013 at 21:52

@PeterAlbert no problem) Being QA has its pros & cons..) – Disinherit 20/2, 2013 at 21:54

@LittleBobbyTables tried that, it didnt' seem to work either. The function just produced a #!VALUE error. – Lucho 20/2, 2013 at 22:16

There are illegal characters in the function you copied and pasted - see my post. – Twice 4/2, 2016 at 10:48

Please use rchako's solution instead, see my comment there for more info. – Remunerative 12/4, 2016 at 15:41

I suppose I don't have to point out that this doesn't generate "real" GUIDs - because they have no version number. See en.wikipedia.org/wiki/Globally_unique_identifier for details – Endomorph 13/4, 2016 at 6:3

S

38

I am using the following function in v.2013 excel vba macro code

Public Function GetGUID() As String 
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36) 
End Function

Simpson answered 17/4, 2014 at 7:8 Comment(5)

couldnt get PeterL's solution to work in Excel 2013.. and after a total of 10 seconds of debugging the function, I found your solution to be a better fit for me. Works perfect. thanks! – Hrutkay 21/8, 2014 at 21:22

This should be used instead of Frazture's answer because it is much more straightforward and relies on the system to generate the guid. Fraxture's solution (no offense) does not even create a valid guid as it doesn't take things such as the current time, MAC address, etc. into account. They also won't include the guid version bit. Guids are not just a set of random characters, there is meaning behind them. More info here: en.wikipedia.org/wiki/Globally_unique_identifier#Algorithm – Remunerative 12/4, 2016 at 15:37

Attention: there may be problem with the library (Permission denied) after installing win update: #45082758 – Rosaline 20/7, 2017 at 11:17

I confirm @Rosaline 's comment that after a security update to Office, this mechanism no longer works. https://mcmap.net/q/57311/-ms-access-vba-error-run-time-error-39-70-39-permission-denied documents the Microsoft recommended methods to prevent the above function from causing a "permission denied" error OR to make use of an alternative function. – Albania 20/9, 2017 at 10:55

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long Public Function GenerateGUID() As String Dim ID(0 To 15) As Byte Dim N As Long Dim GUID As String Dim Res As Long Res = CoCreateGuid(ID(0)) For N = 0 To 15 GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N)) If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then GUID = GUID & "-" End If Next N GenerateGUID = GUID End Function – Simpson 23/8, 2018 at 4:6

P

116

As of modern version of Excel, there's the syntax with commas, not semicolons. I'm posting this answer for convenience of others so they don't have to replace the strings- We're all lazy... hrmp... human, right?

=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,65535),4))

Or, if you like me dislike when a guid screams and shouts and you, we can go lower-cased like this.

=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,65535),4)))

Plaintive answered 7/12, 2016 at 20:24 Comment(17)

Comas and semicolons differs because of localization but not because of modern or not so modern Excel. However good answer – Cholon 8/12, 2017 at 8:40

Perfect! Tested and using – Chromate 17/1, 2019 at 16:5

I needed semicolons, i.e.

=CONCATENATE(DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";DEC2HEX(RANDBETWEEN(0;42949);4);"-";DEC2HEX(RANDBETWEEN(0;42949);4);"-";DEC2HEX(RANDBETWEEN(0;42949);4);"-";DEC2HEX(RANDBETWEEN(0;4294967295);8);DEC2HEX(RANDBETWEEN(0;42949);4))

– Ulyanovsk 15/8, 2019 at 13:54

@Ulyanovsk I think this is my highest upvoted answer ever. Kind of sad because I'm much more skilled in other techs. I'm glad for whatever I get, of course, but still... :) – Plaintive 15/8, 2019 at 16:28

@KonradViltersten - Don't misunderstand me, your answer was useful for me, and I upvoted it. Just wanted to mention that for some reasons, my Excel version wants semicolons instead of commas for separating the parameters - don't know where that can be configured (but as trigras mentioned, surely a localization issue). However, with commas, it won't work on my end, but with semicolons - so I thought it might be useful for some folks to put it in the comments. – Ulyanovsk 16/8, 2019 at 8:36

@Ulyanovsk I can't speak for his experience. I believe that has changes between different version but it might also depend on localization. The important thing is to remember that it's always a subject to change so one should try both this and that if it stops working. Thanks for the upvote. I do enjoy those small tickies, haha. – Plaintive 16/8, 2019 at 21:49

If believe RANDBETWEEN(0,42949) should be RANDBETWEEN(0,65535), otherwise the four digit parts of the GUID will only be between 0000 and A7C5 , whereas they should go all the way to FFFF – Foxworth 26/10, 2021 at 11:46

@ReversedEngineer You are correct. I'm impressed partly because you noticed it and partly because no one else did in almost 5 years! I believe I started with 4294967295, which corresponds to 16^8, i.e. 8 consecutive F. Then, I picked the 4 first initial, decimal digits. My bad. – Plaintive 27/10, 2021 at 5:19

If anybody needs it on a spanish (español) Excel installation:

=CONCATENAR(DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8);"-";DEC.A.HEX(ALEATORIO.ENTRE(0;65536);4);"-";DEC.A.HEX(ALEATORIO.ENTRE(0;65536);4);"-";DEC.A.HEX(ALEATORIO.ENTRE(0;65536);4);"-";DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8);DEC.A.HEX(ALEATORIO.ENTRE(0;65536);4))

– Chloroprene 15/11, 2021 at 15:19

Note that this is not UUID v4. The 3rd group needs to start with 4. – Waylen 24/2, 2023 at 19:35

@GerhardPowell I'm not sure if I'm getting you correctly. Could you provide any reference to support that statement, please? As far I'm aware, there's no restrictions to what any of the defined groups consist of. – Plaintive 25/2, 2023 at 15:59

@KonradViltersten: uuidtools.com/decode – Waylen 3/3, 2023 at 21:27

@GerhardPowell I see that the M-digit denotes version and that may be 4. However, it's not stated that it should be 4. Are you saying that the version the suggested Excel formula produces is 4? How can one determine that? Please help me understand. – Plaintive 4/3, 2023 at 7:56

Random UUIDs are version 4. All the versions are allocated to different purposes. guid.one/guid – Waylen 6/3, 2023 at 22:5

@GerhardPowell I had no idea about that property of GUIDs. I'm humbled and amazed. Thank you very much for educating me. This was nerdily exquisite piece of knowledge. – Plaintive 7/3, 2023 at 19:32

The issue with the formula posted by the user is that there are two null characters (CHAR(0)) in one of the 65535 characters. See my answer below. – Twice 14/4, 2023 at 9:56

@Paul Is that comment addressing the answer of mine, the original question or any of the comments? – Plaintive 15/4, 2023 at 10:31

B

42

=CONCATENATE(
    DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";
    DEC2HEX(RANDBETWEEN(0;42949);4);"-";
    DEC2HEX(RANDBETWEEN(0;42949);4);"-";
    DEC2HEX(RANDBETWEEN(0;42949);4);"-";
    DEC2HEX(RANDBETWEEN(0;4294967295);8);
    DEC2HEX(RANDBETWEEN(0;42949);4)
)

Betti answered 1/12, 2014 at 9:59 Comment(5)

Using Excel 2013 I had to replace all semi-colons with commas. – Psittacine 25/10, 2016 at 15:50

I needed to replace the ";" with "," and then everything worked out great! Thanks :) '=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4))' – Ster 7/12, 2016 at 19:38

If it walks like a GUID and talks like a GUID it's not a GUID. – Fauver 11/5, 2017 at 7:40

Please note that this doesn't generate a valid version 4 GUID/UUID, see en.wikipedia.org/wiki/… The formula in the question is perfectly fine (https://mcmap.net/q/262912/-how-can-i-generate-guids-in-excel), or look at the answers from Masud, Fredder, or nidkil. – Incumbent 24/5, 2019 at 15:50

If believe RANDBETWEEN(0;42949) should be RANDBETWEEN(0;65535), otherwise the four digit parts of the GUID will only be between 0000 and A7C5 , whereas they should go all the way to FFFF – Foxworth 26/10, 2021 at 11:45

S

38

I am using the following function in v.2013 excel vba macro code

Public Function GetGUID() As String 
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36) 
End Function

Simpson answered 17/4, 2014 at 7:8 Comment(5)

couldnt get PeterL's solution to work in Excel 2013.. and after a total of 10 seconds of debugging the function, I found your solution to be a better fit for me. Works perfect. thanks! – Hrutkay 21/8, 2014 at 21:22

This should be used instead of Frazture's answer because it is much more straightforward and relies on the system to generate the guid. Fraxture's solution (no offense) does not even create a valid guid as it doesn't take things such as the current time, MAC address, etc. into account. They also won't include the guid version bit. Guids are not just a set of random characters, there is meaning behind them. More info here: en.wikipedia.org/wiki/Globally_unique_identifier#Algorithm – Remunerative 12/4, 2016 at 15:37

Attention: there may be problem with the library (Permission denied) after installing win update: #45082758 – Rosaline 20/7, 2017 at 11:17

I confirm @Rosaline 's comment that after a security update to Office, this mechanism no longer works. https://mcmap.net/q/57311/-ms-access-vba-error-run-time-error-39-70-39-permission-denied documents the Microsoft recommended methods to prevent the above function from causing a "permission denied" error OR to make use of an alternative function. – Albania 20/9, 2017 at 10:55

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long Public Function GenerateGUID() As String Dim ID(0 To 15) As Byte Dim N As Long Dim GUID As String Dim Res As Long Res = CoCreateGuid(ID(0)) For N = 0 To 15 GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N)) If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then GUID = GUID & "-" End If Next N GenerateGUID = GUID End Function – Simpson 23/8, 2018 at 4:6

B

18

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

Taken from git @mobilitymaster.

Bacchanal answered 17/8, 2017 at 14:38 Comment(7)

I pasted this once into a cell and it seemed to work well. But then I pasted it into another cell and the first cell changed. Then I tried another cell and the first 2 cells changed... why does it do that? – Scabrous 27/11, 2017 at 15:43

That is very interesting observation, I was not aware of that. I used 'outside dragging option' in excel to generate guid in batch. Here is a kind of hacky and incomplete explanation, I found the function =randbetween(x,y) changes its value everytime we hit 'enter' anywhere with a new value. One way to stop the random generation I think is generate the guid in batch using 'outside dragging' and then copy all of them and pasting only the value. – Bacchanal 5/12, 2017 at 1:44

It is due to the Automatic calculation setting on in excel. If you go into Excel Options, then Formulas, then you will see something called "Workbook Calcuation" if you disable it, it will only run the calculations when you manually press the button in the bottom pane. – Decoy 5/11, 2018 at 23:4

WARNING: This has a random constant "4" in the middle of the formula... – Heaps 18/12, 2018 at 15:16

@Sancarn: It generates a valid version 4 GUID/UUID, see en.wikipedia.org/wiki/… (Also note the other 2 fixed bits which are forced by using RANDBETWEEN(8,11)) – Incumbent 24/5, 2019 at 15:48

@SirKillALot interesting, never knew about these. Still have no idea what the point of them is... You're just increasing the likeliness of a duplicate? – Heaps 10/6, 2019 at 20:25

@Sancarn: That's how GUID/UUIDs are defined. In the different versions all those bits and bytes have different meanings (e.g. version 3 and 5 are based on namespace hashes). Only Version 4 is meant to be random, except those couple version and variant bits to distinguish them from the other versions/variants. The Wikipedia article also has en entry about collisions, which is probably correct :-), stating for version 4: "... the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion." – Incumbent 10/6, 2019 at 22:42

T

10

This is not a problem with the function at all.

It took me a bit of digging, but the problem is in copying and pasting. Try copying this: RANDBETWEEN(0,6553‌5) string, posted in your original question, and paste it into a Hex Editor, then you'll see that there are actually two null characters in the 65535:

00000000  52 41 4E 44 42 45 54 57 45 45 4E 28 30 2C 36 35  RANDBETWEEN(0,65
00000010  35 33 00 00 35 29                                53‌..5)

Twice answered 4/2, 2016 at 10:47 Comment(2)

you are the true hacker and genius here – Wivinia 12/7, 2017 at 9:25

@hongsy: Thank-you! True appreciation ;o) – Twice 12/7, 2017 at 12:35

T

3

for me it is correct, in Excel spanish

=CONCATENAR(
DEC.A.HEX(ALEATORIO.ENTRE(0,4294967295),8),"-",
DEC.A.HEX(ALEATORIO.ENTRE(0,65535),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(16384,20479),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(32768,49151),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(0,65535),4),
DEC.A.HEX(ALEATORIO.ENTRE(0,4294967295),8)
)

Twylatwyman answered 15/12, 2016 at 17:42 Comment(0)

H

1

The formula for Dutch Excel:

=KLEINE.LETTERS(
    TEKST.SAMENVOEGEN(
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;8));8);"-";
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;4));4);"-";"4";
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;3));3);"-";
        DEC.N.HEX(ASELECTTUSSEN(8;11));
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;3));3);"-";
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;8));8);
        DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;4));4)
    )
)

Hadhramaut answered 4/12, 2017 at 20:8 Comment(0)

K

0

The formula for French Excel:

=CONCATENER(
DECHEX(ALEA.ENTRE.BORNES(0;4294967295);8);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;4294967295);8);
DECHEX(ALEA.ENTRE.BORNES(0;42949);4))

As noted by Josh M, this does not provide a compliant GUID however, but this works well for my current need.

Kenna answered 25/4, 2017 at 14:42 Comment(1)

For a compliant one see the formula in the actual question, or look at the answers from Masud, Fredder, or nidkil. – Incumbent 24/5, 2019 at 15:52

O

0

The formula for German Excel:

=KLEIN(
    VERKETTEN(
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;8));8);"-";
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;4));4);"-";"4";
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;3));3);"-";
        DEZINHEX(ZUFALLSBEREICH(8;11));
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;3));3);"-";
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;8));8);
        DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;4));
    )
)

Oruntha answered 10/8, 2017 at 16:6 Comment(1)

You may be missing closing brackets ) on this one. – Twice 10/10, 2018 at 7:3

H

0

After trying a number of options and running into various issue with newer versions of Excel (2016) I came across this post from MS that worked like a charm. I enhanced it bit using some code from a post by danwagner.co

Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (Guid As GUID_TYPE) As LongPtr

Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (Guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr

Function CreateGuidString(Optional IncludeHyphens As Boolean = True, Optional IncludeBraces As Boolean = False)
    Dim Guid As GUID_TYPE
    Dim strGuid As String
    Dim retValue As LongPtr

    Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

    retValue = CoCreateGuid(Guid)

    If retValue = 0 Then
        strGuid = String$(guidLength, vbNullChar)
        retValue = StringFromGUID2(Guid, StrPtr(strGuid), guidLength)

        If retValue = guidLength Then
            '   valid GUID as a string
            '   remove them from the GUID
            If Not IncludeHyphens Then
                strGuid = Replace(strGuid, "-", vbNullString, Compare:=vbTextCompare)
            End If

            '   If IncludeBraces is switched from the default False to True,
            '   leave those curly braces be!
            If Not IncludeBraces Then
                strGuid = Replace(strGuid, "{", vbNullString, Compare:=vbTextCompare)
                strGuid = Replace(strGuid, "}", vbNullString, Compare:=vbTextCompare)
            End If


            CreateGuidString = strGuid
        End If
    End If

End Function


Public Sub TestCreateGUID()
    Dim Guid As String
    Guid = CreateGuidString() '<~ default
    Debug.Print Guid
End Sub

There are additional options in the original MS post found here: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2010/guid-run-time-error-70-permission-denied/c9ee4076-98af-4032-bc87-40ad7aa7cb38

Hornbeck answered 29/1, 2019 at 17:56 Comment(2)

If I try this I get the error: User defined type not declared (Windows 10, Excel 2019) – Komarek 2/7, 2020 at 9:46

@Komarek This needs the definition for GUID_TYPE from the previous answer. – Blakeney 19/4, 2021 at 18:48

S

0

Ken Thompson is right! - for me also this way works (excel 2016), but type definition GUID_TYPE is skipped, so full scripting is:

Private Type GUID_TYPE
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (Guid As GUID_TYPE) As LongPtr

Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (Guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr

Function CreateGuidString(Optional IncludeHyphens As Boolean = True, Optional IncludeBraces As Boolean = False)
    Dim Guid As GUID_TYPE
    Dim strGuid As String
    Dim retValue As LongPtr

    Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

    retValue = CoCreateGuid(Guid)

    If retValue = 0 Then
        strGuid = String$(guidLength, vbNullChar)
        retValue = StringFromGUID2(Guid, StrPtr(strGuid), guidLength)

        If retValue = guidLength Then
            '   valid GUID as a string
            '   remove them from the GUID
            If Not IncludeHyphens Then
                strGuid = Replace(strGuid, "-", vbNullString, Compare:=vbTextCompare)
            End If

            '   If IncludeBraces is switched from the default False to True,
            '   leave those curly braces be!
            If Not IncludeBraces Then
                strGuid = Replace(strGuid, "{", vbNullString, Compare:=vbTextCompare)
                strGuid = Replace(strGuid, "}", vbNullString, Compare:=vbTextCompare)
            End If


            CreateGuidString = strGuid
        End If
    End If

End Function

Seltzer answered 5/2, 2020 at 19:33 Comment(0)

D

0

In addition to all the other valuable answers here. For those interested in a universally unique identifier (UUID) version 4 which complies with RFC 4122, I suggest this formula.

=CONCATENATE(
DEC2HEX(RANDBETWEEN(0;4294967296);8);"-";
DEC2HEX(RANDBETWEEN(0;65536);4);"-";
DEC2HEX(RANDBETWEEN(0;65536);4);"-4";
DEC2HEX(RANDBETWEEN(0;4096);3);"-";
DEC2HEX(RANDBETWEEN(0;4294967296);8);
DEC2HEX(RANDBETWEEN(0;65536);4))

Example of random end result 8D8AC610-566D-4EF0-9C22-186B2A5ED793

Below is the answer as above. But with details for those interested.

Assumptions about the formula above:

Your MS Excel or LibreOffice Calc is configured to use English local
Within the UUID third group, the 4 prefix communicates that this UUID is based on UUID version 4. This number if fixed. All the other value may change. Details at:
- https://archive.ph/oCfSA#selection-6225.0-6239.184
- https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#name-version-field

If your MS Excel or LibreOffice is in another local (language), you need to adapt this formula appropriately.

For example, in French the correct formula is this:

=CONCATENER(
DECHEX(ALEA.ENTRE.BORNES(0;4294967296);8);"-";
DECHEX(ALEA.ENTRE.BORNES(0;65536);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;65536);4);"-4";
DECHEX(ALEA.ENTRE.BORNES(0;4096);3);"-";
DECHEX(ALEA.ENTRE.BORNES(0;4294967296);8);
DECHEX(ALEA.ENTRE.BORNES(0;65536);4))

For creating a new UUID, simply exit the cell edit mode, while you view the cell, press F7 key, this will automatically calculate a new UUID.

Attribution to Matt for the inspiration about this formula. Which I adapted for both UUID version 4 format and the maximum amount of Hex values. Which I assume is 16^8 = 4294967296, 16^4 = 65536, 16^3 = 4096.

Discrepant answered 17/4 at 4:0 Comment(0)

K

-1

The formula for Polish version:

=ZŁĄCZ.TEKSTY(
    DZIES.NA.SZESN(LOS.ZAKR(0;4294967295);8);"-";
    DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
    DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
    DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
    DZIES.NA.SZESN(LOS.ZAKR(0;4294967295);8);
    DZIES.NA.SZESN(LOS.ZAKR(0;42949);4)
)

Kaki answered 8/6, 2018 at 8:29 Comment(1)

Please note that this doesn't generate a valid version 4 GUID/UUID, see en.wikipedia.org/wiki/… The formula in the question is perfectly fine (https://mcmap.net/q/262912/-how-can-i-generate-guids-in-excel), or look at the answers from Masud, Fredder, or nidkil. – Incumbent 24/5, 2019 at 15:50

E

-3

Italian version:

=CONCATENA(
    DECIMALE.HEX(CASUALE.TRA(0;4294967295);8);"-";
    DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
    DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
    DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
    DECIMALE.HEX(CASUALE.TRA(0;4294967295);8);
    DECIMALE.HEX(CASUALE.TRA(0;42949);4))

Epistasis answered 16/4, 2018 at 10:32 Comment(1)

Please note that this doesn't generate a valid version 4 GUID/UUID, see en.wikipedia.org/wiki/… The formula in the question is perfectly fine (https://mcmap.net/q/262912/-how-can-i-generate-guids-in-excel), or look at the answers from Masud, Fredder, or nidkil. – Incumbent 24/5, 2019 at 15:49

A

-3

ESP:

=CONCATENAR(
    DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8);"-"; 
    DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-"; 
    DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-"; 
    DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-"; 
    DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8); 
    DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4)
)

Achates answered 18/5, 2018 at 10:11 Comment(1)

Please note that this doesn't generate a valid version 4 GUID/UUID, see en.wikipedia.org/wiki/… The formula in the question is perfectly fine (https://mcmap.net/q/262912/-how-can-i-generate-guids-in-excel), or look at the answers from Masud, Fredder, or nidkil. – Incumbent 24/5, 2019 at 15:49

Recommended topics

Hot tags