CODEWITHSUNDEEP
excelhexxorbitwise-xorvba
Anonymoose
Anonymoose

Reputation: 33

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

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.

Upvotes: 1

Views: 23830

Answers (2)

pnuts
pnuts

Reputation: 59485

With a formula, Text to Columns Fixed width each character separate (say A1:AF1 and A2:AF2): 

=DEC2HEX(BITXOR(HEX2DEC(A1),HEX2DEC(A2)))

copied across to suit. Stitch back together with CONCATENATE if required as a string in a single cell (or copy into Word and Replace all the tabs, ^t, with nothing).

Unfortunately BITXOR was not introduced until Excel 2011 / Excel 2013.

SO33031399 example

Upvotes: 2

Axel Richter
Axel Richter

Reputation: 61900

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.

Upvotes: 1

Related Questions