How to use Excel to calculate bitwise XOR in hexadecimal format?

excel hex xor bitwise-xor vba
Anonymoose picture Anonymoose · Oct 9, 2015 · Viewed 15.8k times · Source

I have two cells with a string of bits:

1747F6001E00DB2XXXXX28FE5257645C
and
1C6262C8DBF510F655XXXXXA3BDA58AC

I want to XOR the two together so that the result would be something like 0B2594C8C5F5CXXXXX190014698D3CF0.

I know that I need to use a bitwise XOR operation in Excel, as bitwise XOR calculators work online, but they give me the option to choose between hexadecimal and binary input data. BITXOR should work, but it does not work for hexadecimal inputs.

Answer

Axel Richter picture Axel Richter · Oct 9, 2015

An approach for two hex-strings in equal length.

Sub approach()

 s1 = "1747F6001E00DB266F5728FE5257645C"
 s2 = "1C6262C8DBF510F6554E28EA3BDA58AC"

 If Len(s1) <> Len(s2) Then Exit Sub

 sRes = ""
 For i = 1 To Len(s1)
  vRes = Val("&H" & Mid(s1, i, 1)) Xor Val("&H" & Mid(s2, i, 1))
  sRes = sRes & Hex(vRes)
 Next

 Debug.Print sRes

End Sub

XOR each digit of the two hex-values as there is no need for an carry since it is not an addition.

Btw. BITXOR will only work up to 2^48-1. You have a value range up to 16^32-1.

Related questions

Convert HEX to RGB in Excel

I have a column "HEX" and three columns "R", "G", and "B". How can I convert a HEX to RGB, e.g. ff0000 to R=255, G=0, and B=0? I know that the first 2 characters ff belongs to "R", the next 2 00 …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More