manipulation of binary numbers

IanC

New Member
Joined
Jan 21, 2004
Messages
12
I have a hexadecimal number that I need to convert to binary, shift the bits and then convert back to decimal again. Let me explain with examples,

The starting hexadecimal number is 5006048ACCC86A32.

I strip off the first 7 digits (5006048), using =RIGHT() function, leaving the remaining hex characters..

A C C C 8 6 A 3 2

I then need to convert each character , one by one, to their binary equivalent, in this case it's ...

1010 1100 1100 1100 1000 0110 1010 0011 0010

..then shift the binary digits two places to the left, and take only the first seven bits. So the binary string becomes

1011 0011 0011 0010 0001 1010 1000

..this then needs to convert each bit back to hex, giving...

B 3 3 2 1 A 8

..finally converting this hex number to decimal to arrive at the answer

B3321A8 hex = 187900328


I can't find out a combination of functions to do this, or should it be VBA ? Any help is greatly appreciated.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Have you looked at HEX2BIN and BIN2HEX in the Analysis Toolpak add-in?
 

IanC

New Member
Joined
Jan 21, 2004
Messages
12
Yes I have. The problem is the manipulation of the numbers and shifting binaries left. In the example below, I take the code I need to convert and then use the mid() function to strip out the individual hex characters. This is then converted one by one to binary, in the following..

But then how do I shift the binary two places to the left and then put together the hex number again, other than treating it like text and using a concatenation of left() and right() functions? Surely there is a clean way of doing this?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What's wrong with using right and left? What would you call a "clean" way of doing it? There's nothing built-in that I know of (not particularly surprising) but you could write a UDF to do it.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
"I then need to convert each character , one by one, to their binary equivalent, in this case it's ...
..then shift the binary digits two places to the left, and take only the first seven bits"

=MOD(A1,2^20)*4

I seem to recall that 4 bits makes a "nibble" i.e. 0010 1011 is 8 bits / 1 byte / 2 nibbles.
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Sorry, I tried to post an image but I'm having problems with that.....

You can use this formula approach where your start data is in A2

B2 has this formula

=HEX2BIN(MID(A2,8,1),4)&HEX2BIN(MID(A2,9,1),4)&HEX2BIN(MID(A2,10,1),4)&HEX2BIN(MID(A2,11,1),4)&HEX2BIN(MID(A2,12,1),4)&HEX2BIN(MID(A2,13,1),4)&HEX2BIN(MID(A2,14,1),4)&HEX2BIN(MID(A2,15,1),4)

then in C2 copied across to I2

=BIN2HEX(MID($B2,COLUMNS($C2:C2)*4-1,4))

Then for the result in J2

=HEX2DEC(C2&D2&E2&F2&G2&H2&I2)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Code version would be something like:
Code:
Function WeirdBinaryHexStuff(varInput) As Long
   Dim strTemp As String, strBin As String
   Dim n As Long
   Dim bytData As Byte
   ' convert to string and strip off first 7
   strTemp = Mid$(CStr(varInput), 8)
   For n = 1 To Len(strTemp)
      strBin = strBin & HexToBin(Mid$(strTemp, n, 1))
   Next n
   strBin = Left$(Mid$(strBin, 3), 28)
   strTemp = ""
   For n = 1 To 28 Step 4
      strTemp = strTemp & BinToHex(Mid$(strBin, n, 4))
   Next n
   WeirdBinaryHexStuff = Val("&H" & strTemp)
End Function
Public Function HexToBin(HexNum As String) As String
   Dim BinNum As String
   Dim lHexNum As Long
   Dim i As Integer
   
   On Error GoTo ErrorHandler
   
'  Check the string for invalid characters
   For i = 1 To Len(HexNum)
      If ((Asc(Mid(HexNum, i, 1)) < 48) Or _
          (Asc(Mid(HexNum, i, 1)) > 57 And _
           Asc(UCase(Mid(HexNum, i, 1))) < 65) Or _
          (Asc(UCase(Mid(HexNum, i, 1))) > 70)) Then
         BinNum = ""
         Err.Raise 1016, "HexToBin", "Invalid Input"
      End If
   Next i
   
   i = 0
   lHexNum = Val("&h" & HexNum)
   Do
      If lHexNum And 2 ^ i Then
         BinNum = "1" & BinNum
      Else
         BinNum = "0" & BinNum
      End If
      i = i + 1
   Loop Until 2 ^ i > 8
'  Return BinNum as a String
   HexToBin = BinNum
ErrorHandler:
End Function
Function BinToHex(BinNum As String) As String
   Dim BinLen As Integer, i As Integer
   Dim HexNum As Variant
   
   On Error GoTo ErrorHandler
   BinLen = Len(BinNum)
   For i = BinLen To 1 Step -1
'     Check the string for invalid characters
      If Asc(Mid(BinNum, i, 1)) < 48 Or _
         Asc(Mid(BinNum, i, 1)) > 49 Then
         HexNum = ""
         Err.Raise 1002, "BinToHex", "Invalid Input"
      End If
'     Calculate HEX value of BinNum
      If Mid(BinNum, i, 1) And 1 Then
         HexNum = HexNum + 2 ^ Abs(i - BinLen)
      End If
   Next i
'  Return HexNum as String
   BinToHex = Hex(HexNum)
ErrorHandler:
End Function
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top