Split binary number into multiple digits in excel

Question

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?

Answer 1

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.

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))