MD5 Hash function in excel?

I would lik开发者_如何转开发e 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?

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

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/

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