StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVBA hash string
text
Body
copied!<p>How do I get a short hash of a long string using Excel VBA</p> <p><strong>Whats given</strong></p> <ul> <li>Input string is not longer than 80 characters</li> <li>Valid input characters are: [0..9] [A_Z] . _ / </li> <li>Valid output characters are [0..9] [A_Z] [a_z] <em>(lower and upper case can be used)</em></li> <li>The output hash shouldn't be longer than ~12 characters (shorter is even better)</li> <li>No need to be unique at all since this will result in a too long hash</li> </ul> <p><strong>What I have done so far</strong></p> <p>I thought <a href="https://stackoverflow.com/a/7360012/935614">this SO answer</a> is a good start since it generates a 4-digit Hex-Code (CRC16). </p> <p>But 4 digits were to little. In my test with 400 strings 20% got a duplicate somewhere else.<br> The chance to generate a collision is too high. </p> <pre class="lang-vb prettyprint-override"><code>Sub tester() For i = 2 To 433 Cells(i, 2) = CRC16(Cells(i, 1)) Next i End Sub Function CRC16(txt As String) Dim x As Long Dim mask, i, j, nC, Crc As Integer Dim c As String Crc = &HFFFF For nC = 1 To Len(txt) j = Val("&H" + Mid(txt, nC, 2)) Crc = Crc Xor j For j = 1 To 8 mask = 0 If Crc / 2 <> Int(Crc / 2) Then mask = &HA001 Crc = Int(Crc / 2) And &H7FFF: Crc = Crc Xor mask Next j Next nC CRC16 = Hex$(Crc) End Function </code></pre> <p><strong>How to reproduce</strong> </p> <p>You can copy these 400 <a href="http://pastebin.com/raw.php?i=wY3E9pTg" rel="nofollow noreferrer">test strings from pastebin</a>.<br> Paste them to column A in a new Excel workbook and execute the code above.</p> <p><strong>Q:</strong> How do I get a string hash which is short enough (12 chars) and long enough to get a small percentage of duplicates.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload