# HEX2BIN of a string

#### screech

##### Active Member
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.

### Excel Facts

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

#### pgc01

##### MrExcel MVP
Hi screech

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

HTH
PGC

#### screech

##### Active Member
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
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
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)

Replies
4
Views
324
Replies
5
Views
65
Replies
2
Views
195
Replies
5
Views
93
Replies
3
Views
53