Excel function for MD5 hashing without VBA
When capturing PII data (Personally identifiable information) in GA or Adobe analytics, you need to make sure that the values captured are encrypted/hashed to respect the rules of these platforms. Otherwise you might have your account deactivated without any prior notice! A very common hashing algorithm is MD5. It produces a 128-bit hash value and it’s a one-way hashing algorithm, meaning that you cannot convert the hashed value back to the original one. (Keep in mind that MD5 hashes are only secure when using a unique input value, to prevent reverse lookup attacks e.g. using https://md5.gromweb.com/ )
To be able to compare and correlated hashed values with offline data you need to also hash (using the same algorithm) the key value of your 2 data sets and then use the hashed values to connect them. Since excel is usually a very convenient tool for reporting and data manipulation below is a an Excel file showing how to get the MD5 hash value without using VBA, just plain Excel functions! This means that you don’t have to mess with additional source code and strange warnings in Excel, when opening the file.
This file is only available for Excel 2013 and above. Office 2013 comes with handy functions for bitwise operations like BITAND(), BITOR(), BITXOR(), BITR[L]SHIFT(). This file can process strings up to 1024 ASCII characters long. This is to reduce the file size (which is already 185 kb). If you need to process longer messages you can add calculation blocks to the bottom of the table (a single calculation block consists of 64 rows).
Credits to Taosique for creating this Excel file. Original post is here
If you prefer using a single Excel function to generate an MD5 hash, you can use Phil Fresle’s VBA source code. By adding this as a class in your Excel file, you’ll be able to use the function “md5hash” to generate MD5 hashed values for any cell value.
