How to create a GUID in Excel?

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?

14 Answers

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

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

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

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

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)

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

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

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.

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

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

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:

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

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

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

Hype Blast

How to create a GUID in Excel?

14 Answers

You Might Also Like

"Here’s what’s going on with Twitter and Substack. Twitter has monopoly mindshare among elite media, and that’s a key part of the audience that Substack is trying to reach. Musk believes that Substack is a potential rival, so he’s trying to maintain his market power by preventing…"

Review: Survivor, "This Is Where The Legends Are Made"

Robert Reich | Substack

How do you beat Endogeny?