# manipulation of binary numbers

#### IanC

##### New Member
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
Have you looked at HEX2BIN and BIN2HEX in the Analysis Toolpak add-in?

#### IanC

##### New Member
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
2Symmcode2B82F9654
3Binary
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

##### MrExcel MVP, Moderator
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
"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
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

##### MrExcel MVP
Here's the sheet......
Book1
ABCDEFGHIJK
1StartnumberConvertcharacters8to15tobinaryShiftandhexResult
25006048ACCC86A32
3
4
 Sheet3

#### RoryA

##### MrExcel MVP, Moderator
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``````

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

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