20

I need a function that will take an entire cell value in Excel and convert the value into its MD5 hash equivalent in a new cell.

Is there a formula in excel that does that? I need a solution that doesn't use VBA. Is this possible?

Melanie Palen
  • 2,645
  • 6
  • 31
  • 50
user2002935
  • 309
  • 1
  • 2
  • 5
  • 9
    *"I need solution without using VBA."* Short answer to this: No. – Tomalak Mar 04 '13 at 12:13
  • 3
    A VBA approach is discussed here: http://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba/125844. You don't say why your requirements prohibit VBA, and I'm not sure there's a non-VBA solution. – Marc Mar 04 '13 at 16:32
  • @user2002935 pure Excel formulas solution is hardly ever possible, and I don't think it's worth the efforts. Look towards VBA and provide your ideas / initial code for community assistance. – Peter L. Mar 04 '13 at 17:04
  • 5
    Everything is possible, you can implement the whole algorithm in multi hidden sheets, then request the user to key the entry in particular cell and retrieve the hash from another cell. The question is, do you really wanna do it this way? – LightYearsBehind May 03 '13 at 04:48
  • What is the data range for the cell values? Is it something you can predict with a given pattern? Like a string sequence? – wittrup Aug 30 '13 at 13:02
  • I could write that for you for a low price. – Unicornist Oct 09 '13 at 20:33
  • I would love to see that done in pure Excel. – ttaaoossuuuu Feb 04 '14 at 11:46
  • 1
    Here is a good past question on this: https://stackoverflow.com/questions/14717526/vba-hash-string/14749855 – Darin Feb 12 '22 at 15:03
  • I made a demo where the excel file consumes a webservice that generate the md5sum for a text (non vba at all). This link will expires in 30 days. https://ufile.io/3olj5nps. Let me know if it will help you to post a proper answer – btafarelo Aug 08 '22 at 12:08

2 Answers2

3

Without VBA is possible to use webservice formula that invoke a webservice that return the md5sum.

Explanation

spreadsheet sample

This web service was developed by me as all the services on internet require a POST method and Excel is only able to perform GET request. It's hosted in a free service and I don't know for how long it will be available.

Web service source code: https://github.com/btafarelo/md5sum

btafarelo
  • 601
  • 3
  • 12
1

This was actually answered (with a "Yes", it's possible, and here's the proof) but the answer has been mysteriously removed. Fortunately, the Wayback Machine remembers the answer. Basically, by using Excel's bitwise operations (BITAND(), BITOR(), BITXOR(), BITR[L]SHIFT()), an MD5 calculation can be made.

Somebody even wrote a blog post on it, including an example workbook.

JKVeganAbroad
  • 169
  • 1
  • 1
  • 11