HEX2BIN of a string

[screech]

Hell everyone:

I have a cell with the text "a09b2010". How to I easily convert that string of characters from hexidecimal to a binary string of 32 characters in one cell -- in this case: 10100000100110110010000000010000? HEX2BIN doesn't seem to work for me when referencing the cell that has the hex code as a value.

 

[pgc01]

Hi screech

You may write a small vba function to replace each Hex code by its binary equivalent.

HTH
PGC

 

[screech]

Hi screech

You may write a small vba function to replace each Hex code by its binary equivalent.

HTH
PGC

I am having a small problem with this. Do I need to find the hex of each character and piece it together as one long binary string or can I just look at the whole hexidecimmal string somehow?

 

[pgc01]

Hi again

This is a possible solution. It seems to work but I didn't give it too much thought. You can try to improve it.

Use it like any other excel formula

=hextobin(A1)

Paste this code in a general module:

Option Explicit
Option Base 1

Function HexToBin(sHexBin As String) As String
Dim sHB, i As Integer

sHB = Array( _
Array("0", "0000"), _
Array("1", "0001"), _
Array("2", "0010"), _
Array("3", "0011"), _
Array("4", "0100"), _
Array("5", "0101"), _
Array("6", "0110"), _
Array("7", "0111"), _
Array("8", "1000"), _
Array("9", "1001"), _
Array("A", "1010"), _
Array("B", "1011"), _
Array("C", "1100"), _
Array("D", "1101"), _
Array("E", "1110"), _
Array("F", "1111"))

For i = 1 To 16
    sHexBin = Replace(sHexBin, sHB(i)(1), sHB(i)(2), Compare:=vbTextCompare)
Next

If Len(Replace(Replace(sHexBin, "0", ""), "1", "")) <> 0 Then
    HexToBin = "Invalid characters"
Else
    HexToBin = "'" & sHexBin
End If
End Function

Hope this helps
PGC

EDIT: Forced HexToBin to return a string

 

[barry houdini]

If your hex number always has 8 characters you could use this formula

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