CODEWITHSUNDEEP
excelexcel-formula
KuldipMK
KuldipMK

Reputation: 1

Hex 2 binary for large number

working in projects, where I need to convert 5 bytes into binary in EXCEL, can anyone please help out Example - Hex A00080080 in to binary in excel cell

Upvotes: 0

Views: 7278

Answers (3)

stefanct
stefanct

Reputation: 2944

There are native bin2hex and hex2bin functions in most excel implementations/clones and they are horrendous (just look at the examples given by M$).

However, you can get around this even without scripts or advanced formula features but the solution is not exactly elegant...

=HEX2BIN(MID(A1,1,2),8)&HEX2BIN(MID(A1,3,2),8)&HEX2BIN(MID(A1,5,2),8)&HEX2BIN(MID(A1,7,2),8)&HEX2BIN(MID(A1,9,2),8)

NB: it requires precisely 2*5 hex characters as input

It can easily be extended further if need be, and also reduced for the probably more commonly required version for 16 and 32 bits, respectively:

=HEX2BIN(MID(A1,1,2),8)&HEX2BIN(MID(A1,3,2),8)
=HEX2BIN(MID(A1,1,2),8)&HEX2BIN(MID(A1,3,2),8)&HEX2BIN(MID(A1,5,2),8)&HEX2BIN(MID(A1,7,2),8)

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

With Excel 365, use:

=TEXTJOIN("",TRUE,HEX2BIN(MID(A1,SEQUENCE(LEN(A1)),1),4))

enter image description here

Note:

  • Each hexal is converted into a 4-bit binary number and then all the 4-bits are concatenated.
  • Because this is just a string-to-string translation, the original hex string can be almost unlimited in length.

Upvotes: 2

Applecore
Applecore

Reputation: 4099

You can use the fact that each hex value is effectively 0-15 (or 0-1111 in binary) to "walk" the hex number and do the conversion character by character within a custom VBA function:

Function fLargeHEX2BIN(strNumber As String) As String
    Dim lngLoop1 As Long
    Dim lngLen As Long
    lngLen = Len(strNumber)
    For lngLoop1 = 1 To lngLen
        fLargeHEX2BIN = fLargeHEX2BIN & Format(WorksheetFunction.Hex2Bin(Mid(strNumber, lngLoop1, 1)), "0000")
    Next lngLoop1
End Function

Regards,

Upvotes: 0

Related Questions