Advanced BIN2HEX Question

compmajor

New Member
Joined
Oct 18, 2006
Messages
1
I need to create a formula that converts an 8-bit binary number to hexidecmal while ignoring the most significant bit. For example, I have a cell where I enter an interger, 224, let's say. I conver that to binary in the cell below using the DEC2BIN(Cell#, 8) formula. Now, in the cell below that I need to convert the binary to hex, but ignore the most significant bit. So if 224 becomes 11100000, I need the hex to be 30 instead of E0. Help!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Been a while since i did my Comp Science course, but am I right in assuming that in the binary number 11100000, the left most 1 is the most significant bit (i.e. with the highest translated value)?

If so, this formula should work for you presuming you have the binary number in cell A1

Code:
=BIN2HEX(RIGHT(A1,7))

However, this returns 60 and not 30.

Patrick
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
An improved attempt:

Code:
=IF(ISERROR((BIN2HEX(RIGHT(A1,(LEN(A1)-FIND("1",A1,1)))))),"Invalid",(BIN2HEX(RIGHT(A1,(LEN(A1)-FIND("1",A1,1))))))

Again, assuming A1 is the cell with the 8-bit binary representation, this will search for the first instance of a 1 to locate the MSB from left to right, and remove that from the calculation.


edit: northwolves, you have missed a zero, which is why you are getting the incorrect decimal conversion.
 

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122

ADVERTISEMENT

Code:
=DEC2HEX(MOD(224,128))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I need to create a formula that converts an 8-bit binary number to hexidecmal while ignoring the most significant bit. For example, I have a cell where I enter an interger, 224, let's say. I conver that to binary in the cell below using the DEC2BIN(Cell#, 8) formula. Now, in the cell below that I need to convert the binary to hex, but ignore the most significant bit. So if 224 becomes 11100000, I need the hex to be 30 instead of E0. Help!

So if A1 contains, say, 54. You convert this to binary in A2 resulting in

00110110

ignoring the most significant bit this becomes

00010110

converted to a hex number = 16

If this is what you mean then you could do this without reference to the binary number, i.e. using this formula where A1 contains your initial decimal number

=DEC2HEX(A1-2^FLOOR(LOG(A1,2),1))

....alternatively....by referencing your binary number in A2.....

=BIN2HEX(SUBSTITUTE(A2,1,0,1))
 

Forum statistics

Threads
1,136,989
Messages
5,678,975
Members
419,796
Latest member
doctorgresham

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