# HEX Numbers, Hex to Bin

#### nickgreening

##### New Member
I would like to convert a Hex number to Binary number. When i convert ff it works correctly, but when i convert E97 i get an message saying #¡NUM!
i think this is because th number E97 is seen by excel as text and not a number, but FF is okay! don't understand!!! i am using Excel 2003 SP2, thanks

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
When i convert ff it works
... tell how you are converting please.

Hi Nick

From the Excel Help~:

If number is negative, it cannot be less than FFFFFFFE00, and if number is positive, it cannot be greater than 1FF.

So the number is too big for HexToBin.

Best regards

Richard

Ah, Richard, well spotted.

thanks, guys

You can use this formula to convert positive HEX to binary accurately up to 7FFF

=(DEC2BIN(HEX2DEC(B2)/64)&DEC2BIN(MOD(HEX2DEC(B2),64),6))+0

where your hex number is in A1

edit:"simplified" formula

Arbitraging the formulas! Where on Earth do you get this stuff from Barry?

It's really just a variation on a way to extend DEC2BIN so that it can give 18 bit results

=DEC2BIN((A1/512),9)&DEC2BIN(MOD(A1,512),9)

In fact it's one of the very first formulas I posted on these boards, although, if memory serves, I pinched it from another (inferior) board.

http://www.mrexcel.com/board2/viewtopic.php?t=137980&highlight=bin2dec

Replies
6
Views
418
Replies
3
Views
124
Replies
6
Views
269
Replies
1
Views
225
Replies
18
Views
924

1,218,920
Messages
6,145,227
Members
450,603
Latest member
jpbegley

### 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?

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