I work in eDiscovery and I have a bunch of data that I'm attempting to make hashes for. Unfortunately, this data has emails that were stored in a program called Bloomberg Vault. It seems when an email is stored in Bloomberg Vault it puts all email addresses in alphabetical order. Because we are loading data that wasn't stored in this program and data that was stored in this program we are unable to generate matching hashes for the data.

I originally concatenated the data in SQL and assumed there was a function built-in that would alphabetize the data. Unfortunately, this isn't actually a function as I needed it in SQL. Then, I figured I would be able to wing it in Excel, but haven't found the particular solution I need. I found a function that sorts a cell based on a delimiter, and that almost works for me, except I don't have consistent delimiters in the data. I could insert them at concatenation in SQL, but these are many thousands of characters long cells of data and it wouldn't be possible for me to verify that the delimiters went into equal places.

So, an example. I have two strings one looks like <TO: David, Larry@cye.org; Benes, Elaine@seinfeld.org;> and another string that is the opposite <TO: Benes, Elaine@seinfeld.org; David, Larry@cye.org;>. (These strings also contain FROM, CC, BCC, Subject, and IntMsgID).

I need a function that just sorts all of the cell in alphabetical order, spaces can be ignored, but symbol characters need to have a consistent sort as well. Because I'm just making a hash I don't care what the data looks like afterward. I want both strings to look like

After I have those strings sorted alphabetically I'll be able to plug them back into SQL, then use HashBytes on them to generate the hashes. I appreciate if anyone can help.

Thank you,

Joe Halvarson