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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Have you looked at HEX2BIN and BIN2HEX in the Analysis Toolpak add-in?
 
Upvote 0
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..
WWN converter.xls
BCDEFGHIJKL
1
2Symmcode2B82F96542B82F9654
3Binary001010111000001011111001011001010100
4
Sheet1 (2)


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?
 
Upvote 0
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.
 
Upvote 0
"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:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top