+ Reply to Thread
Results 1 to 3 of 3

Decimal to 16 bits binary conversion in Excel?

Hybrid View

  1. #1
    Registered User
    Join Date
    09-25-2019
    Location
    New York
    MS-Off Ver
    2016
    Posts
    1

    Decimal to 16 bits binary conversion in Excel?

    By using the DEC2HEX with the character count to 10, the number of bits can be increased up to 40. The minimum number is -549755813888 (HEX 8000000000)and the maximum number is 549755813887 (HEX 7FFFFFFFFF). Each pair of HEX characters represents a bit array of 8. Use the formulas below:


    For the following formulas, the number is entered into A1

    An 8 BIT array requires only the last 2 characters to be used.

    =HEX2BIN(RIGHT(DEC2HEX(A1,10),2),8)


    A 16 BIT array simply uses the MID formula for the next 2 HEX characters starting at location 7.

    =HEX2BIN(MID(DEC2HEX(A1,10),7,2),8)&HEX2BIN(RIGHT(DEC2HEX(A1,10),2),8)


    Add another 8 bits to the array by simply adding to the left of the formula with the starting point 2 less than the previous.

    =HEX2BIN(MID(DEC2HEX(A1,10),5,2),8)&HEX2BIN(MID(DEC2HEX(A1,10),7,2),8)&HEX2BIN(RIGHT(DEC2HEX(A1,10),2),8)



    and so on.


    Cheers!
    Last edited by AliGW; 07-31-2020 at 12:45 PM.

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,463

    Re: decimal to 16 bits binary conversion in Excel?

    This is probably overkill, but if you are willing to use a UDF (user defined function) created in VBA code, then here is a function (with two supporting functions that it needs to make use of) that will handle up to 95 bit addition or subtraction. The function is named BinAddSub. Note that the binary numbers on the worksheet should be placed in cell formatted as Text. To use the function just pass it the main binary number and the binary number you want to add or subtract from it. If you are adding, you do not have to specify the optional third argument because addition is the default action (you can supply a quoted plus sign for the third argument if you wish); however if you are doing a subtraction, you must tell the function that by providing a quoted minus sign as the third argument. You should format the cell you put the formula using this UDF in as Text to preserve leading zeroes. Also, the function returns as many bits as there are in the first argument. Here is the code...
    Function BinAddSub(FirstBin As String, SecondBin As String, Optional AddSub As String = "+") As String
      If AddSub = "+" Then
        BinAddSub = Format(DecToBin(BinToDec(FirstBin) + BinToDec(SecondBin)), String(Len(FirstBin), "0"))
      Else
        BinAddSub = Format(DecToBin(BinToDec(FirstBin) - BinToDec(SecondBin)), String(Len(FirstBin), "0"))
      End If
    End Function
    
    ' The DecimalIn argument is limited to 79228162514264337593543950266
    ' (approximately 96-bits) - large numerical values must be entered
    ' as a String value to prevent conversion to scientific notation.
    Function DecToBin(ByVal DecimalIn As Variant, _
                      Optional NumberOfBits As Variant) As String
      DecToBin = ""
      DecimalIn = CDec(DecimalIn)
      Do While DecimalIn <> 0
        DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
        DecimalIn = Int(DecimalIn / 2)
      Loop
      If Not IsMissing(NumberOfBits) Then
        If Len(DecToBin) > NumberOfBits Then
          DecToBin = "Error - Number too large for bit size"
        Else
          DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin, NumberOfBits)
        End If
      End If
    End Function
    
    ' BinaryString argument can be a maximum of 96 digits (either 0's or 1's)
    Function BinToDec(BinaryString As String) As Variant
      Dim X As Integer
      Const TwoToThe48 As Variant = 281474976710656#
      For X = 0 To Len(BinaryString) - 1
        If X > 48 Then
          BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, Len(BinaryString) - _
                     X, 1)) * TwoToThe48 * CDec(2 ^ (X - 48))
        Else
          BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
                     Len(BinaryString) - X, 1)) * CDec(2 ^ X)
        End If
      Next
      If Len(BinToDec) > 10 Then BinToDec = CStr(BinToDec)
    End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste all of the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

    =BinAddSub(A1,A2)
    =BinAddSub(A1,A2,"+")
    =BinAddSub(A1,A2,"-")

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,259

    Re: Decimal to 16 bits binary conversion in Excel?

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to the thread to which you posted, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    I have moved your question and Rick's response to a new thread for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto New Tab renamed....
    By gavinsmith2121 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2017, 11:49 AM
  2. Values to be renamed automatically
    By studio52 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2014, 10:01 AM
  3. Suggestion-Thread Unread Notification For Editing/Change In Thread Title
    By :) Sixthsense :) in forum Suggestions for Improvement
    Replies: 5
    Last Post: 01-15-2014, 01:54 AM
  4. Prevent sheets from being renamed
    By LAF in forum Excel General
    Replies: 3
    Last Post: 02-01-2010, 04:30 PM
  5. pivottables: renamed fields and renamed data
    By lalbatros in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2007, 03:30 PM
  6. [SOLVED] Renamed ranges - PART 2
    By rm81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2006, 09:00 AM
  7. Renamed invalid sheet name
    By Angela in forum Excel General
    Replies: 7
    Last Post: 02-17-2006, 11:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1