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.
 

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
Joined
Apr 25, 2006
Messages
19,884
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,884
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top