Is there an Excel function to create a hash value?
up vote
21
down vote
favorite
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP
from another workseet or workbook.
I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
microsoft-excel-2010 worksheet-function hashing
add a comment |
up vote
21
down vote
favorite
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP
from another workseet or workbook.
I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
microsoft-excel-2010 worksheet-function hashing
add a comment |
up vote
21
down vote
favorite
up vote
21
down vote
favorite
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP
from another workseet or workbook.
I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
microsoft-excel-2010 worksheet-function hashing
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP
from another workseet or workbook.
I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
microsoft-excel-2010 worksheet-function hashing
microsoft-excel-2010 worksheet-function hashing
edited Jan 29 '14 at 13:53
Andrea
1,42631215
1,42631215
asked Feb 13 '13 at 14:35
dwwilson66
759102039
759102039
add a comment |
add a comment |
6 Answers
6
active
oldest
votes
up vote
29
down vote
accepted
You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with
=BASE64SHA1(A1)
in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library "Microsoft MSXML" (with late binding)
Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
Dim enc As Object
Dim TextToHash() As Byte
Dim SharedSecretKey() As Byte
Dim bytes() As Byte
Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
SharedSecretKey = asc.GetBytes_4(sTextToHash)
enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
BASE64SHA1 = EncodeBase64(bytes)
BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
Set enc = Nothing
End Function
Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line:
Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
|
show 3 more comments
up vote
4
down vote
I don't care very much about collisions, but needed a weak pseudorandomizer of
rows based on a variable-length string field. Here's one insane solution that worked well:
=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)
Where Z2
is the cell containing the string you want to hash.
"MOD"s are there to prevent overflowing to scientific notation. 1009
is a prime, could use anything X so that X*255 < max_int_size
. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
add a comment |
up vote
3
down vote
For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.
E.g.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Edit:
If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
so that the lengths are a meaningful scrambler.
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
add a comment |
up vote
1
down vote
You can try this. Run a Pseudo# on two columns:
=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))
Where A1 and B1 store random seeds entered manually: 0
add a comment |
up vote
0
down vote
To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.
However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP
will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes oftitle
in my frozen left pane...
– dwwilson66
Feb 13 '13 at 15:11
add a comment |
up vote
0
down vote
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.
=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))
It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.
add a comment |
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
29
down vote
accepted
You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with
=BASE64SHA1(A1)
in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library "Microsoft MSXML" (with late binding)
Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
Dim enc As Object
Dim TextToHash() As Byte
Dim SharedSecretKey() As Byte
Dim bytes() As Byte
Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
SharedSecretKey = asc.GetBytes_4(sTextToHash)
enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
BASE64SHA1 = EncodeBase64(bytes)
BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
Set enc = Nothing
End Function
Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line:
Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
|
show 3 more comments
up vote
29
down vote
accepted
You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with
=BASE64SHA1(A1)
in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library "Microsoft MSXML" (with late binding)
Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
Dim enc As Object
Dim TextToHash() As Byte
Dim SharedSecretKey() As Byte
Dim bytes() As Byte
Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
SharedSecretKey = asc.GetBytes_4(sTextToHash)
enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
BASE64SHA1 = EncodeBase64(bytes)
BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
Set enc = Nothing
End Function
Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line:
Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
|
show 3 more comments
up vote
29
down vote
accepted
up vote
29
down vote
accepted
You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with
=BASE64SHA1(A1)
in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library "Microsoft MSXML" (with late binding)
Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
Dim enc As Object
Dim TextToHash() As Byte
Dim SharedSecretKey() As Byte
Dim bytes() As Byte
Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
SharedSecretKey = asc.GetBytes_4(sTextToHash)
enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
BASE64SHA1 = EncodeBase64(bytes)
BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
Set enc = Nothing
End Function
Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line:
Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with
=BASE64SHA1(A1)
in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library "Microsoft MSXML" (with late binding)
Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
Dim enc As Object
Dim TextToHash() As Byte
Dim SharedSecretKey() As Byte
Dim bytes() As Byte
Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
SharedSecretKey = asc.GetBytes_4(sTextToHash)
enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
BASE64SHA1 = EncodeBase64(bytes)
BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
Set enc = Nothing
End Function
Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line:
Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
edited Nov 16 at 16:23
answered Feb 13 '13 at 14:58
nixda
20.5k777130
20.5k777130
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
|
show 3 more comments
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?
– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
1
1
Looks great. However, I don't have enough VBA experience to prevent Excel from returning
#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?– dwwilson66
Feb 13 '13 at 16:09
Looks great. However, I don't have enough VBA experience to prevent Excel from returning
#NAME?
. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow?– dwwilson66
Feb 13 '13 at 16:09
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code
– dwwilson66
Feb 13 '13 at 16:54
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now!
– dwwilson66
Feb 13 '13 at 17:03
1
1
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
This is an excellent tool.
– Jay Killeen
Aug 23 '15 at 14:48
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
How large can the source data be and still retain 0% collision?
– Vegard
Mar 31 '16 at 9:45
|
show 3 more comments
up vote
4
down vote
I don't care very much about collisions, but needed a weak pseudorandomizer of
rows based on a variable-length string field. Here's one insane solution that worked well:
=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)
Where Z2
is the cell containing the string you want to hash.
"MOD"s are there to prevent overflowing to scientific notation. 1009
is a prime, could use anything X so that X*255 < max_int_size
. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
add a comment |
up vote
4
down vote
I don't care very much about collisions, but needed a weak pseudorandomizer of
rows based on a variable-length string field. Here's one insane solution that worked well:
=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)
Where Z2
is the cell containing the string you want to hash.
"MOD"s are there to prevent overflowing to scientific notation. 1009
is a prime, could use anything X so that X*255 < max_int_size
. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
add a comment |
up vote
4
down vote
up vote
4
down vote
I don't care very much about collisions, but needed a weak pseudorandomizer of
rows based on a variable-length string field. Here's one insane solution that worked well:
=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)
Where Z2
is the cell containing the string you want to hash.
"MOD"s are there to prevent overflowing to scientific notation. 1009
is a prime, could use anything X so that X*255 < max_int_size
. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
I don't care very much about collisions, but needed a weak pseudorandomizer of
rows based on a variable-length string field. Here's one insane solution that worked well:
=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)
Where Z2
is the cell containing the string you want to hash.
"MOD"s are there to prevent overflowing to scientific notation. 1009
is a prime, could use anything X so that X*255 < max_int_size
. 10 is arbitrary; use anything. "Else" values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
edited May 30 at 14:50
ArtB
2621415
2621415
answered May 13 '16 at 19:56
Anonymous Coward
411
411
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
add a comment |
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
Honestly this is the simplest answer, I doubt collisions are an issue for most excel use cases.
– rolls
Aug 1 at 1:52
add a comment |
up vote
3
down vote
For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.
E.g.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Edit:
If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
so that the lengths are a meaningful scrambler.
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
add a comment |
up vote
3
down vote
For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.
E.g.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Edit:
If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
so that the lengths are a meaningful scrambler.
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
add a comment |
up vote
3
down vote
up vote
3
down vote
For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.
E.g.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Edit:
If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
so that the lengths are a meaningful scrambler.
For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.
E.g.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Edit:
If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
so that the lengths are a meaningful scrambler.
edited Jun 13 '13 at 18:33
answered Jun 13 '13 at 14:48
Assad Ebrahim
1,39111523
1,39111523
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
add a comment |
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
Great answer! (: "poor man's hash function", "caveat", "how it works" :)
– nutty about natty
Aug 8 at 8:11
1
1
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
To "inspect the results to see that there are no collisions" you could simply try / test this by running DATA > REMOVE DUPLICATES and see if there are any. [obviously / presumably, if you do encouter duplicates you could just re-run the above function for these iteratively until no duplicates are left]
– nutty about natty
Aug 8 at 8:15
add a comment |
up vote
1
down vote
You can try this. Run a Pseudo# on two columns:
=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))
Where A1 and B1 store random seeds entered manually: 0
add a comment |
up vote
1
down vote
You can try this. Run a Pseudo# on two columns:
=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))
Where A1 and B1 store random seeds entered manually: 0
add a comment |
up vote
1
down vote
up vote
1
down vote
You can try this. Run a Pseudo# on two columns:
=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))
Where A1 and B1 store random seeds entered manually: 0
You can try this. Run a Pseudo# on two columns:
=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))
Where A1 and B1 store random seeds entered manually: 0
edited Nov 5 '13 at 17:04
Mogget
1,048922
1,048922
answered Nov 5 '13 at 16:24
Michael Polubinski
111
111
add a comment |
add a comment |
up vote
0
down vote
To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.
However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP
will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes oftitle
in my frozen left pane...
– dwwilson66
Feb 13 '13 at 15:11
add a comment |
up vote
0
down vote
To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.
However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP
will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes oftitle
in my frozen left pane...
– dwwilson66
Feb 13 '13 at 15:11
add a comment |
up vote
0
down vote
up vote
0
down vote
To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.
However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP
will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...
To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.
However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP
will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...
answered Feb 13 '13 at 14:40
Peter Albert
2,62211223
2,62211223
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes oftitle
in my frozen left pane...
– dwwilson66
Feb 13 '13 at 15:11
add a comment |
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes oftitle
in my frozen left pane...
– dwwilson66
Feb 13 '13 at 15:11
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of
title
in my frozen left pane...– dwwilson66
Feb 13 '13 at 15:11
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of
title
in my frozen left pane...– dwwilson66
Feb 13 '13 at 15:11
add a comment |
up vote
0
down vote
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.
=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))
It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.
add a comment |
up vote
0
down vote
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.
=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))
It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.
add a comment |
up vote
0
down vote
up vote
0
down vote
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.
=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))
It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1.
=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))
It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.
answered Sep 21 at 16:16
Ant Cole
1
1
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f550592%2fis-there-an-excel-function-to-create-a-hash-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown