How to create a GUID in Excel

Question

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   quot - quot  DEC2HEX RANDBETWEEN 0 6553  5  4   quot - quot       DEC2HEX RANDBETWEEN 16384 20479  4   quot - quot  DEC2HEX RANDBETWEEN 32768 49151    4   quot - quot       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  quot  amp  quot  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The formula for Polish version    ZLACZ 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

User · Answer

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

User · Answer

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

User · Answer

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     lt   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

User · Answer

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 42949  4   -  DEC2HEX RANDBETWEEN 0 42949  4   -  DEC2HEX RANDBETWEEN 0 42949  4   -  DEC2HEX RANDBETWEEN 0 4294967295  8  DEC2HEX RANDBETWEEN 0 42949  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 42949  4   -  DEC2HEX RANDBETWEEN 0 42949  4   -  DEC2HEX RANDBETWEEN 0 42949  4   -  DEC2HEX RANDBETWEEN 0 4294967295  8  DEC2HEX RANDBETWEEN 0 42949  4

User · Answer

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

User · Answer

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

User · Answer

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

[excel] How to create a GUID in Excel?

Examples related to excel

Examples related to guid