14

I would like to convert a number of excel cells in my document from a serial number to the MD5 hash of that serial number. Is there a precompiled formula in excel that does that, or is my only option to do VBA. If VBA, how would I do it?

Anon21
  • 2,961
  • 6
  • 37
  • 46
  • I think it's already been answered over here: http://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba – NinjaCat Aug 17 '10 at 00:06
  • You can try Google Spreadsheet with Google Scripts. Easy to use with great open source projects. – northtree Oct 28 '13 at 10:59

3 Answers3

10

Some links in the question Password hash function for Excel VBA are now broken. Here is an updated version of the accepted answer on that question :

You'll find an implementation for VB and VBScript here:
http://web.archive.org/web/20080526064101/http://www.frez.co.uk/freecode.htm#md5

I believe it would be quite easy to port to excel.

However someone has already done that. Unfortunately the solution is at experts-exchange which doesn't allow straight links. So we have to go through Google. Click here to perform a Google search and then click the first result. Scroll down a lot to see the accepted solution.

Source : Password hash function for Excel VBA

Community
  • 1
  • 1
HoLyVieR
  • 10,985
  • 5
  • 42
  • 67
  • :D Wow, since when can you view answers on Experts Exchange for free? I remember you had to register before, so I haven't been there for years now. – casablanca Aug 17 '10 at 00:24
  • 3
    @casablanca If you get to a expert exchange question from a Google link, you will be able to access the answers. If they didn't let you see it, they would have been banned from Google long time ago. It's against Google policy to display different content to Google Bot and normal user. – HoLyVieR Aug 17 '10 at 00:34
  • 2
    God bless Stack overflow. – lewis Aug 04 '15 at 22:41
1

I found the most convenient solution here: https://www.mrexcel.com/board/threads/convert-string-to-md5-hash.973381/

It leverages the .NET API, rather than encoding it all in VB, so should be nice and fast.

https://learn.microsoft.com/en-us/dotnet/api/system.security.cryptography.md5cryptoserviceprovider?view=net-6.0

Add the following VBA code:

Function StringToMD5Hex(ByVal s As String) As String
Dim enc As Object
Dim bytes() As Byte
Dim pos As Long
Dim outstr As String

Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")

bytes = StrConv(s, vbFromUnicode)
bytes = enc.ComputeHash_2(bytes)

For pos = LBound(bytes) To UBound(bytes)
   outstr = outstr & LCase(Right("0" & Hex(bytes(pos)), 2))
Next pos

StringToMD5Hex = outstr
Set enc = Nothing
End Function

Then call it using:

=StringToMD5Hex("string to hash")
=StringToMD5Hex(A2)

As an aside, if you're interested in how MD5 works, this pure excel implementation is a great learning tool: https://tzamtzis.gr/2017/web-analytics/excel-function-md5-hashing-without-vba/

O Couch
  • 54
  • 4
0

I see that this question is old, but I needed something similar and though I could share how I solved the problem.

Create a Module and insert this code:

Function stringToUTFBytes(aString)
    Dim UTF8
    Set UTF8 = CreateObject("System.Text.UTF8Encoding")
    stringToUTFBytes = UTF8.GetBytes_4(aString)
End Function
Function md5hashBytes(aBytes)
    Dim MD5
    Set MD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    md5hashBytes = MD5.ComputeHash_2((aBytes))
End Function
Function bytesToHex(aBytes)
    Dim hexStr, x
    For x = 1 To LenB(aBytes)
        hexStr = Hex(AscB(MidB((aBytes), x, 1)))
        If Len(hexStr) = 1 Then hexStr = "0" & hexStr
        bytesToHex = bytesToHex & hexStr
    Next
End Function

To call MD5, you can use:

bytesToHex(md5hashBytes(stringToUTFBytes("change here")))
Arthur Gelcer
  • 49
  • 7