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?
-
9"I need solution without using VBA." Short answer to this: No.Tomalak– Tomalak2013-03-04 12:13:38 +00:00Commented Mar 4, 2013 at 12:13
-
3A VBA approach is discussed here: stackoverflow.com/questions/125785/…. You don't say why your requirements prohibit VBA, and I'm not sure there's a non-VBA solution.Marc– Marc2013-03-04 16:32:54 +00:00Commented Mar 4, 2013 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.– Peter L.2013-03-04 17:04:25 +00:00Commented Mar 4, 2013 at 17:04
-
5Everything 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– LightYearsBehind2013-05-03 04:48:22 +00:00Commented May 3, 2013 at 4:48
-
1Here is a good past question on this: stackoverflow.com/questions/14717526/vba-hash-string/14749855Darin– Darin2022-02-12 15:03:32 +00:00Commented Feb 12, 2022 at 15:03
|
Show 4 more comments
2 Answers
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.
1 Comment
Nelson
Confirmed this is valid MD5 hashing by comparing to other sources of MD5 hashing. Note that the entire file size is 185kb and the ONLY THING it does is calculate the MD5. It is over 1000 lines of calculations.
Without VBA is possible to use webservice formula that invoke a webservice that return the md5sum.
Explanation
=WEBSERVICE(B4)
- Result: 89374b6dda3bb0e16132b21fb9887f1d
=CONCAT("https://md5sum.herokuapp.com/?text=";ENCODEURL(B3))
B3 cells contains the text to be coded
- Value: bruno tafarelo
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
