Split binary number into multiple digits in excel

user_9332988 0
Viewed 3176

I have a decimal number and I can convert it into binary using DEC2BIN(cell value) function in excel. Right now it gives the binary value in a single cell, instead, I want each digit in the binary number into multiple cells.

Data:

  A          |B         |C       |D       | E      --- Excel Columns
1|21
2|DEC2BIN(A1)
3|
4|

Current result: Data:

  A          |B         |C       |D       | E      --- Excel Columns
1|21
2|10101
3|
4|

Desired Result: Data:

  A          |B         |C       |D       | E      --- Excel Columns
1|21
2|1          0          1        0        1
3|
4|

Is this possible? If so how?

excel
2 Answers

In A2:

=MID(DEC2BIN($A$1),COLUMN(A1),1)

Then copy it across. You can drag it across in excess of what you need. This will prepare it for use in case you are going to have larger numbers. That way, you will not have to constantly update the code.

enter image description here

Alternatively, if you are planning on using this code for multiple binary entries, you could put the number in column A then copy the following down as far as you need:

=IF(ISBLANK($A1),"",MID(DEC2BIN($A1),COLUMN(A1),1))

enter image description here

user_41012100

If one has the Dynamic Array formula:

=TRANSPOSE(MID(DEC2BIN(A1),SEQUENCE(LEN(DEC2BIN(A1))),1))

in A2 and Excel will spill the result across.

enter image description here

user_48515900
Related