Break single hexadecimal number up into its 4 binary components

[AnotherSami]

Hello all,

I am trying to convert a single hexadecimal number into its 4 constituent binary bits in 4 different columns while preserving the leading zeros. I have seen multiple answers on this and other forums, but non preserve the leading zeros.

below is an example of what I am trying to do. If I have the 0x1 i want to return 0001 in 4 different columns. Same with 0x4 returning 0100 ... and so on.

lmk04208 Registers.txt
	N	O	P	Q	R
33	hex	B3	B2	B1	B0
34	1	0	0	0	1
35	4	0	1	0	0
36	6	0	1	1	0
37	F	1	1	1	1
lmk04208 Registers

Thanks in advance!

 

[Z51]

In O34 and filled right/down, try:

=MID(TEXT(HEX2BIN($N34),"0000"),COLUMN(A1),1)

The "A1" reference returns 1, 2, 3 and 4 when dragged across (for binary digits 1-4), so don't change that to N1.

 

[rlv01]

tmptest.xlsm
	A	B	C	D	E
1	hex	B3	B2	B1	B0
2	1	0	0	0	1
3	4	0	1	0	0
4	6	0	1	1	0
5	F	1	1	1	1
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(HEX2BIN($A2,4),1)
C2:C5	C2	=MID(HEX2BIN($A2,4),2,1)
D2:D5	D2	=MID(HEX2BIN($A2,4),3,1)
E2:E5	E2	=RIGHT(HEX2BIN($A2,4),1)

 

[Z51]

Ah, didn't realize hex2bin had a second argument... haha. You can then use the MID formula for all places like in my original, without the TEXT bits and no need to use four different formulas.

=MID(HEX2BIN($N1,4),COLUMN(A1),1)