manipulation of binary numbers

[IanC]

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]

Have you looked at HEX2BIN and BIN2HEX in the Analysis Toolpak add-in?

 

[IanC]

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
	B	C	D	E	F	G	H	I	J	K	L
1
2	Symmcode	2B82F9654	2	B	8	2	F	9	6	5	4
3	Binary		0010	1011	1000	0010	1111	1001	0110	0101	0100
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?

 

[RoryA]

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]

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

 

[barry houdini]

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)

 

[barry houdini]

Here's the sheet......

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Startnumber	Convertcharacters8to15tobinary	Shiftandhex							Result
2	5006048ACCC86A32	10101100110011001000011010100011	B	3	3	2	1	A	8	187900328
3
4
Sheet3

 

[RoryA]

Code version would be something like:

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