# 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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### 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
212
Replies
9
Views
178
Replies
7
Views
126
Replies
3
Views
224
Replies
6
Views
135

1,141,012
Messages
5,703,729
Members
421,312
Latest member
Mooncake1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back