Hexadecimal to binary

[me]

Can someone point me to an online resource or explain how I can convert a
hexadecimal number into a binary number whilst also retaining the leading
zeros.
Some of the hex numbers are too large for the HEX2BIN function. I need the
leading zeros in the result so I can look at specific chunks bit values
using MID().

Tia

 

[Gary''s Student]

Use chunking to convert large hex values one hexal at a time:

Let's say we have 1abc in A1. HEX2BIN() won't work. In A2 thru A5 put:

=HEX2BIN(LEFT(A1))
=HEX2BIN(MID(A1,2,1))
=HEX2BIN(MID(A1,3,1))
=HEX2BIN(RIGHT(A1))
and you will see:

1
1010
1011
1100

in A6 put:
=A2&A3&A4&A5
to see:
1101010111100
all 13 bits
You can now concatenate enough leading zeros to make any fixed length.

 

[me]

Many thanks.

 

[me]

Is there a way to get the result of the formula to display to a specified
format so that in your example the first HEX2BIN would return 0001 as the
answer? Custom formats don't seem to work on the results of a formula.

 

[me]

Found it.

=HEX2BIN(LEFT(A3,2),8) & HEX2BIN(RIGHT(A3,2),8)

 

[Gary''s Student]

A very good solution. Once I realized that HEX2BIN was really just playing
with strings, solutions became a lot easier to find.


Have a pleasant weekend