EXCEL – How to convert HEX to BIN (All Sizes)

In this tutorial, I am going to show you how to convert Hexadecimal to Binary 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 between hex and binary in Excel.

The Excel Software has a generic function called HEX2BIN which allows you to convert hexadecimal 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 hexadecimal word of any size is explained.

HEX2BIN Excel generic function

Excel has a generic function that allows you to convert hexadecimal words to binary format. This is called HEX2BIN and it is used according to the following expression:

• =HEX2BIN(number, [SizeBinaryWord])

The HEX2BIN function syntax has the following arguments:

• Number – The hexadecimal word that you want to convert. Number cannot contain more than 10 binary characters (result). 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)
• SizeBinaryWord – The number of characters to use. If SizeBinaryWord is omitted, HEX2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros). (Optional)

You can put this function in a worksheet, as exemplified in Figure 1:

If you enter a hexadecimal word that has a binary result 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 result

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 Hexadecimal word with any size, and the result is a Binary word with a size equal to the minimum number of necessary bits.

Code

This code is written in VBA language and can be changed according to the user’s needs. Enjoy it!!

Public Function MY_HEX2BIN(strHEX As String) As String
    Dim charac As Long, indexes As Long, i As Long
    Dim zeros As String * 4
    For charac = 1 To Len(strHEX)
        indexes = 0
        zeros = "0000"
        i = Val("&H" & Mid$(strHEX, charac, 1))
        While i > 0
            Mid$(zeros, 4 - indexes, 1) = i Mod 2
            i = i \ 2
            indexes = indexes + 1
        Wend
        MY_HEX2BIN = MY_HEX2BIN & zeros & ""
    Next
    MY_HEX2BIN = RTrim$(MY_HEX2BIN)
End Function

References

https://www.exceltip.com/excel-functions/how-to-use-the-hex2bin-function-in-excel.html

https://support.microsoft.com/en-us/office/hex2bin-function-a13aafaa-5737-4920-8424-643e581828c1