HEX2BIN of a string

screech

Active Member
Joined
May 27, 2004
Messages
296
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi screech

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

HTH
PGC
 

screech

Active Member
Joined
May 27, 2004
Messages
296
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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:

Code:
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,451
Members
410,612
Latest member
MrACED
Top