In this tutorial, I am going to show you how to convert Binary to Hexadecimal format using an Excel worksheet and some VBA code.
Hex numbers and binary numbers are usually used in computer technology, but do you know how to convert hex numbers to binary numbers and vice versa? Now I will tell you the easy way to solve the conversion from Binary Words to Hexadecimal Words in Excel. If you want to learn how to convert Hex to Binary, you should click here.
The Excel Software has a generic function called BIN2HEX which allows you to convert binary words up to 10 size characters (result bits). Sometimes, it is necessary to convert more than 10 bits, so in the first part of this tutorial the generic Excel function is presented and in the second part a way to convert a binary word of any size is explained.
BIN2HEX Excel generic function
Excel has a generic function that allows you to convert binary words to hexadecimal format. This is called BIN2HEX and it is used according to the following expression:
- =BIN2HEX(number, [SizeHexadecimalWord])
The BIN2HEX function syntax has the following arguments:
- Number – The binary word that you want to convert. Number cannot contain more than 10 binary characters. The most significant bit of number is the sign bit (40th bit from the right). The remaining 9 bits are magnitude bits. Negative numbers are represented using two’s-complement notation. (Required)
- SizeHexadecimalWord – The number of characters to use. If SizeHexadecimalWord is omitted, BIN2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0’s (zeros). (Optional)
You can put this function in a worksheet, this is exemplified in Figure 1:
If you enter a binary word greater than 10 bits, the result will be a conversion error as you can see in the following figure:
Solution for words greater than 10 bits
For this solution, it is necessary to create a module using VBA code, as you can see in the following steps. The VBA code is presented in Code Section.
1. In the Developer tab, click on Visual Basic Button or press (Alt+F11) on your keyboard.
2. This will open a new window, called Microsoft Visual Basic for Applications;
3. If you have more than one Excel worksheet open, you must select the worksheet where you want to create your Hex2Bin conversion function, as you can see in Figure 4;
4. In the Insert tab, click on Module. This action creates a new module that allows the insertion of VBA code;
5. Copy the code to the empty Module created. The result should be similar to Figure 5;
Note: Don’t forget to save or do (Ctrl+S)
6. In your Excel worksheet you can call your function in any cell, as you can see in the following figure.
You can insert a Binary word with any size, and the result is a Hexadecimal word with a size equal to the minimum number of necessary characters.
Code
This code is written in VBA language and can be changed according to the user’s needs. Enjoy it!!
Function MY_BIN2HEX$(bin$)
Dim c&, i&, j&, hNdx&, nibble&, bits$, s$
Dim b() As Byte, h() As Byte
Static bHexChars() As Byte, pow2() As Byte
Const HEX_CHARS$ = "0123456789ABCDEF"
If (Not Not bHexChars) = 0 Then bHexChars = StrConv(HEX_CHARS, vbFromUnicode)
If (Not Not pow2) = 0 Then pow2 = ChrW$(&H201) & ChrW$(&H804)
b = StrConv(bin, vbFromUnicode)
ReDim h(0 To -Int(-Len(bin) / 4) - 1)
hNdx = UBound(h)
For i = UBound(b) To 0 Step -1
If b(i) = 49& Then nibble = nibble + pow2(c)
c = c + 1
If c = 4 Then
h(hNdx) = bHexChars(nibble)
hNdx = hNdx - 1
nibble = 0
c = 0
End If
Next
If c Then h(hNdx) = bHexChars(nibble)
MY_BIN2HEX = StrConv(h, vbUnicode)
End Function
