Presenting a number as an indication of a 32 bit string

funkey_monkey

Hi,

I'm trying to convert a number in the range 1 - 32 which represents a bit position into a 32 bit string representation.

For example:

Number   Value
1        0b00000000000000000000000000000001
2        0b00000000000000000000000000000010
3        0b00000000000000000000000000000100
...
8        0b00000000000000000000000010000000
...
31       0b01000000000000000000000000000000
32       0b10000000000000000000000000000000

I was using:

Cell B1 =TEXT(DEC2BIN(POWER(2,A1-1)), 00000000000000000000000000000000)

Output = CONCATENATE("0b", B1)

However, I've just realised that DEC2BIN doesn't like going over 2^8. So, looking for another way to covert the number from # to 0b00000000000000000000000000000000.

I'm assuming that a macro is the best way to go about this, but I've not done any macros in over a decade now.

funkey_monkey

If this would be better in the development forum could a mod please move.

Capt'n Midnight

You could use DEC2HEX , then map each character to 4 binary bits in adjacent cells .

smuggler.ie

If you would be looking for binary could use...
cell B1 ->> =BASE(A1,2,32)


..., but seams you are not.

funkey_monkey

There is always someone who has done something similar before...

Take my number, convert the bit position into a decimal representation using power function.

Then use the formula provided here to get the value in bit representation:

https://stackoverflow.com/questions/24387894/decimal-to-binary-conversion-for-large-numbers-in-excel

smuggler.ie

funkey_monkey wrote: »

There is always someone who has done something similar before...

Truth...
Dont mind to share what you did end up with?

funkey_monkey

CELL C2 - This gives the bit value

=POWER(2, F2-1)

CELL E2 - This is the bit set in a 32-bit longword

=DEC2BIN(MOD(QUOTIENT(C2,256^3),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^2),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^0),256),8)

CELL F2 This is the bit position - manually entered

1

CELL G2 - This is the final output which I needed

=CONCATENATE("0b", TEXT(E2, "00000000000000000000000000000000"))

smuggler.ie

funkey_monkey wrote: »

CELL G2 - This is the final output which I needed

=CONCATENATE("0b", TEXT([B]E2[/B], "00000000000000000000000000000000"))

It must be typo, you mean E2 D2

Then basically one long, convoluted formula in any desired cell providing you maintain manual data entry in F2 (or mod formula to acomodate)

=CONCATENATE("0b", TEXT(DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^3),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^2),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^1),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^0),256),8), "00000000000000000000000000000000"))

Not that i can determine use of it....

funkey_monkey

smuggler.ie wrote: »

It must be typo, you mean D2

I had a blank col between C and E. E2 was correct, but essentially the same as what you've said. I should have said CELL E2 instead of CELL D2. Corrected in above post.

Then basically one long, convoluted formula in any desired cell providing you maintain manual data entry in F2 (or mod formula to acomodate)

=CONCATENATE("0b", TEXT(DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^3),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^2),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^1),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^0),256),8), "00000000000000000000000000000000"))

Not that i can determine use of it....

Basically DEC2BIN only works on small values - 511. So, split it down into small chunks and convert them on a piece by piece basis and then finally concatenate them all together at the end.
Without sitting down and fully working through it, it uses QUOTIENT function to find the integer part for each calculation and then uses the MOD function to pass on to next stage.


However, if you have a fresher version of Excel then it is simply:

=BASE(3,2)  # returns 11

=BASE(11,2)  # returns 1011

So, I would just have to use

=BASE(C2, 2)

- I think as I've no way of checking it.

TheChizler

=REPT("0",32-NUMBER)&"1"&REPT("0",NUMBER-1)

1 00000000000000000000000000000001
2 00000000000000000000000000000010
3 00000000000000000000000000000100
8 00000000000000000000000010000000
31 01000000000000000000000000000000
32 10000000000000000000000000000000

smuggler.ie

nested formula i posted is workout from what you provided, just keep your sheet clean, no need for hidden columns


I am under impression that =BASE() is not what you desire.
your(nested) formula returns

11 0b00000000000000000000010000000000 11'th bit from right

were =BASE(F2,2,32)
11 00000000000000000000000000001011 binary of decimal 11


TheChizler line so neat

funkey_monkey

TheChizler wrote: »

=REPT("0",32-NUMBER)&"1"&REPT("0",NUMBER-1)

1 00000000000000000000000000000001
2 00000000000000000000000000000010
3 00000000000000000000000000000100
8 00000000000000000000000010000000
31 01000000000000000000000000000000
32 10000000000000000000000000000000

Great soln that.

funkey_monkey

smuggler.ie wrote: »

nested formula i posted is workout from what you provided, just keep your sheet clean, no need for hidden columns


I am under impression that =BASE() is not what you desire.
your(nested) formula returns

11 0b00000000000000000000010000000000 11'th bit from right

were =BASE(F2,2,32)
11 00000000000000000000000000001011 binary of decimal 11


TheChizler line so neat

It would be working off Col C2 - which for 11 would use BASE(1024, 2). But yes, when you know your way around the Excel functions there is always a better solution.

smuggler.ie

What is it for?

funkey_monkey

Its part of a command file for testing some hardware. A tool was written which expects the values in a certain format and this has been nailed down to the point where no deviations in types etc will work.
I've not looked into the guts of it as I'm busy enough with this part, but I guess that there is a parser in the tool which expects each line in a specific format.

smuggler.ie

funkey_monkey wrote: »

Its part of a command file for testing some hardware. A tool was written which expects the values in a certain format and this has been nailed down to the point where no deviations in types etc will work.
I've not looked into the guts of it as I'm busy enough with this part, but I guess that there is a parser in the tool which expects each line in a specific format.

OK
As long its not binary that it takes IN as those two are much different...