How to create a GUID in Excel?

excel guid
fraxture picture fraxture · Feb 20, 2013 · Viewed 104.3k times · Source

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?

Answer

Konrad Viltersten picture Konrad Viltersten · Dec 7, 2016

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

Related questions

How can I generate GUIDs in Excel?

I have an excel file with one order on each row, and I want each order to have a unique identifier, so there will be a Unique ID column. Every time I fill a row, I want Excel to automatically …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More