Is there an Excel function to create a hash value?











up vote
21
down vote

favorite
11












I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.



I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?



Any thoughts or ideas on this or another strategy?










share|improve this question




























    up vote
    21
    down vote

    favorite
    11












    I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.



    I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?



    Any thoughts or ideas on this or another strategy?










    share|improve this question


























      up vote
      21
      down vote

      favorite
      11









      up vote
      21
      down vote

      favorite
      11






      11





      I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.



      I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?



      Any thoughts or ideas on this or another strategy?










      share|improve this question















      I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.



      I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?



      Any thoughts or ideas on this or another strategy?







      microsoft-excel-2010 worksheet-function hashing






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 29 '14 at 13:53









      Andrea

      1,42631215




      1,42631215










      asked Feb 13 '13 at 14:35









      dwwilson66

      759102039




      759102039






















          6 Answers
          6






          active

          oldest

          votes

















          up vote
          29
          down vote



          accepted










          You don't need to write your own function - others already did that for you.

          For example I collected and compared five VBA hash functions on this stackoverflow answer



          Personally I use this VBA function




          • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module

          • requires .NET since it uses the library "Microsoft MSXML" (with late binding)




          Public Function BASE64SHA1(ByVal sTextToHash As String)

          Dim asc As Object
          Dim enc As Object
          Dim TextToHash() As Byte
          Dim SharedSecretKey() As Byte
          Dim bytes() As Byte
          Const cutoff As Integer = 5

          Set asc = CreateObject("System.Text.UTF8Encoding")
          Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

          TextToHash = asc.GetBytes_4(sTextToHash)
          SharedSecretKey = asc.GetBytes_4(sTextToHash)
          enc.Key = SharedSecretKey

          bytes = enc.ComputeHash_2((TextToHash))
          BASE64SHA1 = EncodeBase64(bytes)
          BASE64SHA1 = Left(BASE64SHA1, cutoff)

          Set asc = Nothing
          Set enc = Nothing

          End Function

          Private Function EncodeBase64(ByRef arrData() As Byte) As String

          Dim objXML As Object
          Dim objNode As Object

          Set objXML = CreateObject("MSXML2.DOMDocument")
          Set objNode = objXML.createElement("b64")

          objNode.DataType = "bin.base64"
          objNode.nodeTypedValue = arrData
          EncodeBase64 = objNode.text

          Set objNode = Nothing
          Set objXML = Nothing

          End Function


          Customizing the hash length




          • the hash initially is a 28 characters long unicode string (case sensitive + special chars)

          • You customize the hash length with this line: Const cutoff As Integer = 5

          • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate

          • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate


          There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.



          You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet






          share|improve this answer



















          • 1




            Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
            – dwwilson66
            Feb 13 '13 at 16:09












          • Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
            – dwwilson66
            Feb 13 '13 at 16:54












          • WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
            – dwwilson66
            Feb 13 '13 at 17:03






          • 1




            This is an excellent tool.
            – Jay Killeen
            Aug 23 '15 at 14:48










          • How large can the source data be and still retain 0% collision?
            – Vegard
            Mar 31 '16 at 9:45


















          up vote
          4
          down vote













          I don't care very much about collisions, but needed a weak pseudorandomizer of
          rows based on a variable-length string field. Here's one insane solution that worked well:



          =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)


          Where Z2 is the cell containing the string you want to hash.



          "MOD"s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.






          share|improve this answer























          • Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
            – rolls
            Aug 1 at 1:52


















          up vote
          3
          down vote













          For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.



          E.g.



           =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))


          Here A1 and B1 hold a random start letter and string length.



          A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.



          How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.



          CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.



          Edit:
          If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:



           =CODE(TRIM(C8))*LEN(TRIM(C8))
          +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))


          so that the lengths are a meaningful scrambler.






          share|improve this answer























          • Great answer! (: "poor man's hash function", "caveat", "how it works" :)
            – nutty about natty
            Aug 8 at 8:11






          • 1




            To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
            – nutty about natty
            Aug 8 at 8:15


















          up vote
          1
          down vote













          You can try this. Run a Pseudo# on two columns:




          =+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))




          Where A1 and B1 store random seeds entered manually: 0






          share|improve this answer






























            up vote
            0
            down vote













            To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.



            However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...






            share|improve this answer





















            • yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
              – dwwilson66
              Feb 13 '13 at 15:11


















            up vote
            0
            down vote













            I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.



            =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))


            It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.






            share|improve this answer





















              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "3"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














               

              draft saved


              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f550592%2fis-there-an-excel-function-to-create-a-hash-value%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              6 Answers
              6






              active

              oldest

              votes








              6 Answers
              6






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              29
              down vote



              accepted










              You don't need to write your own function - others already did that for you.

              For example I collected and compared five VBA hash functions on this stackoverflow answer



              Personally I use this VBA function




              • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module

              • requires .NET since it uses the library "Microsoft MSXML" (with late binding)




              Public Function BASE64SHA1(ByVal sTextToHash As String)

              Dim asc As Object
              Dim enc As Object
              Dim TextToHash() As Byte
              Dim SharedSecretKey() As Byte
              Dim bytes() As Byte
              Const cutoff As Integer = 5

              Set asc = CreateObject("System.Text.UTF8Encoding")
              Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

              TextToHash = asc.GetBytes_4(sTextToHash)
              SharedSecretKey = asc.GetBytes_4(sTextToHash)
              enc.Key = SharedSecretKey

              bytes = enc.ComputeHash_2((TextToHash))
              BASE64SHA1 = EncodeBase64(bytes)
              BASE64SHA1 = Left(BASE64SHA1, cutoff)

              Set asc = Nothing
              Set enc = Nothing

              End Function

              Private Function EncodeBase64(ByRef arrData() As Byte) As String

              Dim objXML As Object
              Dim objNode As Object

              Set objXML = CreateObject("MSXML2.DOMDocument")
              Set objNode = objXML.createElement("b64")

              objNode.DataType = "bin.base64"
              objNode.nodeTypedValue = arrData
              EncodeBase64 = objNode.text

              Set objNode = Nothing
              Set objXML = Nothing

              End Function


              Customizing the hash length




              • the hash initially is a 28 characters long unicode string (case sensitive + special chars)

              • You customize the hash length with this line: Const cutoff As Integer = 5

              • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate

              • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate


              There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.



              You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet






              share|improve this answer



















              • 1




                Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
                – dwwilson66
                Feb 13 '13 at 16:09












              • Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
                – dwwilson66
                Feb 13 '13 at 16:54












              • WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
                – dwwilson66
                Feb 13 '13 at 17:03






              • 1




                This is an excellent tool.
                – Jay Killeen
                Aug 23 '15 at 14:48










              • How large can the source data be and still retain 0% collision?
                – Vegard
                Mar 31 '16 at 9:45















              up vote
              29
              down vote



              accepted










              You don't need to write your own function - others already did that for you.

              For example I collected and compared five VBA hash functions on this stackoverflow answer



              Personally I use this VBA function




              • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module

              • requires .NET since it uses the library "Microsoft MSXML" (with late binding)




              Public Function BASE64SHA1(ByVal sTextToHash As String)

              Dim asc As Object
              Dim enc As Object
              Dim TextToHash() As Byte
              Dim SharedSecretKey() As Byte
              Dim bytes() As Byte
              Const cutoff As Integer = 5

              Set asc = CreateObject("System.Text.UTF8Encoding")
              Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

              TextToHash = asc.GetBytes_4(sTextToHash)
              SharedSecretKey = asc.GetBytes_4(sTextToHash)
              enc.Key = SharedSecretKey

              bytes = enc.ComputeHash_2((TextToHash))
              BASE64SHA1 = EncodeBase64(bytes)
              BASE64SHA1 = Left(BASE64SHA1, cutoff)

              Set asc = Nothing
              Set enc = Nothing

              End Function

              Private Function EncodeBase64(ByRef arrData() As Byte) As String

              Dim objXML As Object
              Dim objNode As Object

              Set objXML = CreateObject("MSXML2.DOMDocument")
              Set objNode = objXML.createElement("b64")

              objNode.DataType = "bin.base64"
              objNode.nodeTypedValue = arrData
              EncodeBase64 = objNode.text

              Set objNode = Nothing
              Set objXML = Nothing

              End Function


              Customizing the hash length




              • the hash initially is a 28 characters long unicode string (case sensitive + special chars)

              • You customize the hash length with this line: Const cutoff As Integer = 5

              • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate

              • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate


              There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.



              You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet






              share|improve this answer



















              • 1




                Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
                – dwwilson66
                Feb 13 '13 at 16:09












              • Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
                – dwwilson66
                Feb 13 '13 at 16:54












              • WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
                – dwwilson66
                Feb 13 '13 at 17:03






              • 1




                This is an excellent tool.
                – Jay Killeen
                Aug 23 '15 at 14:48










              • How large can the source data be and still retain 0% collision?
                – Vegard
                Mar 31 '16 at 9:45













              up vote
              29
              down vote



              accepted







              up vote
              29
              down vote



              accepted






              You don't need to write your own function - others already did that for you.

              For example I collected and compared five VBA hash functions on this stackoverflow answer



              Personally I use this VBA function




              • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module

              • requires .NET since it uses the library "Microsoft MSXML" (with late binding)




              Public Function BASE64SHA1(ByVal sTextToHash As String)

              Dim asc As Object
              Dim enc As Object
              Dim TextToHash() As Byte
              Dim SharedSecretKey() As Byte
              Dim bytes() As Byte
              Const cutoff As Integer = 5

              Set asc = CreateObject("System.Text.UTF8Encoding")
              Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

              TextToHash = asc.GetBytes_4(sTextToHash)
              SharedSecretKey = asc.GetBytes_4(sTextToHash)
              enc.Key = SharedSecretKey

              bytes = enc.ComputeHash_2((TextToHash))
              BASE64SHA1 = EncodeBase64(bytes)
              BASE64SHA1 = Left(BASE64SHA1, cutoff)

              Set asc = Nothing
              Set enc = Nothing

              End Function

              Private Function EncodeBase64(ByRef arrData() As Byte) As String

              Dim objXML As Object
              Dim objNode As Object

              Set objXML = CreateObject("MSXML2.DOMDocument")
              Set objNode = objXML.createElement("b64")

              objNode.DataType = "bin.base64"
              objNode.nodeTypedValue = arrData
              EncodeBase64 = objNode.text

              Set objNode = Nothing
              Set objXML = Nothing

              End Function


              Customizing the hash length




              • the hash initially is a 28 characters long unicode string (case sensitive + special chars)

              • You customize the hash length with this line: Const cutoff As Integer = 5

              • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate

              • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate


              There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.



              You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet






              share|improve this answer














              You don't need to write your own function - others already did that for you.

              For example I collected and compared five VBA hash functions on this stackoverflow answer



              Personally I use this VBA function




              • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module

              • requires .NET since it uses the library "Microsoft MSXML" (with late binding)




              Public Function BASE64SHA1(ByVal sTextToHash As String)

              Dim asc As Object
              Dim enc As Object
              Dim TextToHash() As Byte
              Dim SharedSecretKey() As Byte
              Dim bytes() As Byte
              Const cutoff As Integer = 5

              Set asc = CreateObject("System.Text.UTF8Encoding")
              Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

              TextToHash = asc.GetBytes_4(sTextToHash)
              SharedSecretKey = asc.GetBytes_4(sTextToHash)
              enc.Key = SharedSecretKey

              bytes = enc.ComputeHash_2((TextToHash))
              BASE64SHA1 = EncodeBase64(bytes)
              BASE64SHA1 = Left(BASE64SHA1, cutoff)

              Set asc = Nothing
              Set enc = Nothing

              End Function

              Private Function EncodeBase64(ByRef arrData() As Byte) As String

              Dim objXML As Object
              Dim objNode As Object

              Set objXML = CreateObject("MSXML2.DOMDocument")
              Set objNode = objXML.createElement("b64")

              objNode.DataType = "bin.base64"
              objNode.nodeTypedValue = arrData
              EncodeBase64 = objNode.text

              Set objNode = Nothing
              Set objXML = Nothing

              End Function


              Customizing the hash length




              • the hash initially is a 28 characters long unicode string (case sensitive + special chars)

              • You customize the hash length with this line: Const cutoff As Integer = 5

              • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate

              • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate


              There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.



              You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Nov 16 at 16:23

























              answered Feb 13 '13 at 14:58









              nixda

              20.5k777130




              20.5k777130








              • 1




                Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
                – dwwilson66
                Feb 13 '13 at 16:09












              • Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
                – dwwilson66
                Feb 13 '13 at 16:54












              • WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
                – dwwilson66
                Feb 13 '13 at 17:03






              • 1




                This is an excellent tool.
                – Jay Killeen
                Aug 23 '15 at 14:48










              • How large can the source data be and still retain 0% collision?
                – Vegard
                Mar 31 '16 at 9:45














              • 1




                Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
                – dwwilson66
                Feb 13 '13 at 16:09












              • Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
                – dwwilson66
                Feb 13 '13 at 16:54












              • WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
                – dwwilson66
                Feb 13 '13 at 17:03






              • 1




                This is an excellent tool.
                – Jay Killeen
                Aug 23 '15 at 14:48










              • How large can the source data be and still retain 0% collision?
                – Vegard
                Mar 31 '16 at 9:45








              1




              1




              Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
              – dwwilson66
              Feb 13 '13 at 16:09






              Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
              – dwwilson66
              Feb 13 '13 at 16:09














              Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
              – dwwilson66
              Feb 13 '13 at 16:54






              Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
              – dwwilson66
              Feb 13 '13 at 16:54














              WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
              – dwwilson66
              Feb 13 '13 at 17:03




              WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
              – dwwilson66
              Feb 13 '13 at 17:03




              1




              1




              This is an excellent tool.
              – Jay Killeen
              Aug 23 '15 at 14:48




              This is an excellent tool.
              – Jay Killeen
              Aug 23 '15 at 14:48












              How large can the source data be and still retain 0% collision?
              – Vegard
              Mar 31 '16 at 9:45




              How large can the source data be and still retain 0% collision?
              – Vegard
              Mar 31 '16 at 9:45












              up vote
              4
              down vote













              I don't care very much about collisions, but needed a weak pseudorandomizer of
              rows based on a variable-length string field. Here's one insane solution that worked well:



              =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)


              Where Z2 is the cell containing the string you want to hash.



              "MOD"s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.






              share|improve this answer























              • Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
                – rolls
                Aug 1 at 1:52















              up vote
              4
              down vote













              I don't care very much about collisions, but needed a weak pseudorandomizer of
              rows based on a variable-length string field. Here's one insane solution that worked well:



              =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)


              Where Z2 is the cell containing the string you want to hash.



              "MOD"s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.






              share|improve this answer























              • Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
                – rolls
                Aug 1 at 1:52













              up vote
              4
              down vote










              up vote
              4
              down vote









              I don't care very much about collisions, but needed a weak pseudorandomizer of
              rows based on a variable-length string field. Here's one insane solution that worked well:



              =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)


              Where Z2 is the cell containing the string you want to hash.



              "MOD"s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.






              share|improve this answer














              I don't care very much about collisions, but needed a weak pseudorandomizer of
              rows based on a variable-length string field. Here's one insane solution that worked well:



              =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)


              Where Z2 is the cell containing the string you want to hash.



              "MOD"s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited May 30 at 14:50









              ArtB

              2621415




              2621415










              answered May 13 '16 at 19:56









              Anonymous Coward

              411




              411












              • Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
                – rolls
                Aug 1 at 1:52


















              • Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
                – rolls
                Aug 1 at 1:52
















              Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
              – rolls
              Aug 1 at 1:52




              Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
              – rolls
              Aug 1 at 1:52










              up vote
              3
              down vote













              For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.



              E.g.



               =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))


              Here A1 and B1 hold a random start letter and string length.



              A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.



              How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.



              CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.



              Edit:
              If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:



               =CODE(TRIM(C8))*LEN(TRIM(C8))
              +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))


              so that the lengths are a meaningful scrambler.






              share|improve this answer























              • Great answer! (: "poor man's hash function", "caveat", "how it works" :)
                – nutty about natty
                Aug 8 at 8:11






              • 1




                To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
                – nutty about natty
                Aug 8 at 8:15















              up vote
              3
              down vote













              For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.



              E.g.



               =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))


              Here A1 and B1 hold a random start letter and string length.



              A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.



              How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.



              CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.



              Edit:
              If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:



               =CODE(TRIM(C8))*LEN(TRIM(C8))
              +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))


              so that the lengths are a meaningful scrambler.






              share|improve this answer























              • Great answer! (: "poor man's hash function", "caveat", "how it works" :)
                – nutty about natty
                Aug 8 at 8:11






              • 1




                To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
                – nutty about natty
                Aug 8 at 8:15













              up vote
              3
              down vote










              up vote
              3
              down vote









              For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.



              E.g.



               =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))


              Here A1 and B1 hold a random start letter and string length.



              A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.



              How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.



              CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.



              Edit:
              If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:



               =CODE(TRIM(C8))*LEN(TRIM(C8))
              +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))


              so that the lengths are a meaningful scrambler.






              share|improve this answer














              For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.



              E.g.



               =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))


              Here A1 and B1 hold a random start letter and string length.



              A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.



              How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.



              CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.



              Edit:
              If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:



               =CODE(TRIM(C8))*LEN(TRIM(C8))
              +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))


              so that the lengths are a meaningful scrambler.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Jun 13 '13 at 18:33

























              answered Jun 13 '13 at 14:48









              Assad Ebrahim

              1,39111523




              1,39111523












              • Great answer! (: "poor man's hash function", "caveat", "how it works" :)
                – nutty about natty
                Aug 8 at 8:11






              • 1




                To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
                – nutty about natty
                Aug 8 at 8:15


















              • Great answer! (: "poor man's hash function", "caveat", "how it works" :)
                – nutty about natty
                Aug 8 at 8:11






              • 1




                To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
                – nutty about natty
                Aug 8 at 8:15
















              Great answer! (: "poor man's hash function", "caveat", "how it works" :)
              – nutty about natty
              Aug 8 at 8:11




              Great answer! (: "poor man's hash function", "caveat", "how it works" :)
              – nutty about natty
              Aug 8 at 8:11




              1




              1




              To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
              – nutty about natty
              Aug 8 at 8:15




              To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
              – nutty about natty
              Aug 8 at 8:15










              up vote
              1
              down vote













              You can try this. Run a Pseudo# on two columns:




              =+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))




              Where A1 and B1 store random seeds entered manually: 0






              share|improve this answer



























                up vote
                1
                down vote













                You can try this. Run a Pseudo# on two columns:




                =+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))




                Where A1 and B1 store random seeds entered manually: 0






                share|improve this answer

























                  up vote
                  1
                  down vote










                  up vote
                  1
                  down vote









                  You can try this. Run a Pseudo# on two columns:




                  =+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))




                  Where A1 and B1 store random seeds entered manually: 0






                  share|improve this answer














                  You can try this. Run a Pseudo# on two columns:




                  =+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))




                  Where A1 and B1 store random seeds entered manually: 0







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 5 '13 at 17:04









                  Mogget

                  1,048922




                  1,048922










                  answered Nov 5 '13 at 16:24









                  Michael Polubinski

                  111




                  111






















                      up vote
                      0
                      down vote













                      To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.



                      However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...






                      share|improve this answer





















                      • yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                        – dwwilson66
                        Feb 13 '13 at 15:11















                      up vote
                      0
                      down vote













                      To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.



                      However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...






                      share|improve this answer





















                      • yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                        – dwwilson66
                        Feb 13 '13 at 15:11













                      up vote
                      0
                      down vote










                      up vote
                      0
                      down vote









                      To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.



                      However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...






                      share|improve this answer












                      To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.



                      However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...







                      share|improve this answer












                      share|improve this answer



                      share|improve this answer










                      answered Feb 13 '13 at 14:40









                      Peter Albert

                      2,62211223




                      2,62211223












                      • yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                        – dwwilson66
                        Feb 13 '13 at 15:11


















                      • yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                        – dwwilson66
                        Feb 13 '13 at 15:11
















                      yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                      – dwwilson66
                      Feb 13 '13 at 15:11




                      yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane...
                      – dwwilson66
                      Feb 13 '13 at 15:11










                      up vote
                      0
                      down vote













                      I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.



                      =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))


                      It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.






                      share|improve this answer

























                        up vote
                        0
                        down vote













                        I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.



                        =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))


                        It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.






                        share|improve this answer























                          up vote
                          0
                          down vote










                          up vote
                          0
                          down vote









                          I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.



                          =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))


                          It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.






                          share|improve this answer












                          I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.



                          =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))


                          It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Sep 21 at 16:16









                          Ant Cole

                          1




                          1






























                               

                              draft saved


                              draft discarded



















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f550592%2fis-there-an-excel-function-to-create-a-hash-value%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Plaza Victoria

                              Puebla de Zaragoza

                              Musa