cell formatting zero padding on binary no's

R

RossK

I'm struggling through doing some binary math on excel. Ugh. You'd think
Excel is written by programmers, so they would see the value of having
some programmer-friendly bitwise math and binary, octal and hex formatting
stuff in there, if for no other reason than they could use it themselves.

Anyway, that's my rant, here's my question


I have some numbers in cells that I convert to binary

C5: 55 DEC2BIN(C5) gives me 1010101

I'd really like to display that as

0101 0101

.... ie with a leading zero and a space between the nibbles.


It appears that cell formatting (for example which which allows me to show
a number 357 as 00035) does not work with binary numbers! Or am I doing
something wrong. I went into cell format and made a custom format of

0000 0000

and it doesn't do anything for me. When I apply that same formatting to a
number like 286 it becomes:

0000 0286

which is what I expect it to do. Any way to make that work for binary?


Thanks in advance....


Ross.
 
D

Dave Peterson

maybe something like:

=TEXT(--DEC2BIN(C5),"0000 0000")

The -- coerces the text to a number.
 
E

Earl Kiosterud

Ross,

The interesting thing about this is that 101 0101 isn't the binary for
decimal 55. The binary for 55 decimal is 110111. 55 hex is 0101 0101 in
binary, though.
 
Top