We have updated our Privacy Notice, you can read the updated document here
Mods please check the Moderators Group for an important update on Mod tools. If you do not have access to the group, please PM Niamh. Thanks!

Presenting a number as an indication of a 32 bit string

  • #2
    Registered Users Posts: 5,386 ✭✭✭ 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.


Comments

  • #2


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


  • #2


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


  • #2


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


    ..., but seams you are not.


  • #2


    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


  • #2


    There is always someone who has done something similar before...
    Truth...
    Dont mind to share what you did end up with?


  • #2


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


  • #2


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


  • #2


    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.


  • #2


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

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


  • #2


    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


  • #2


    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.


  • #2


    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.


  • #2


    What is it for?


  • #2


    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.


  • #2


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


Society & Culture