Fortunately, there are a Number of functions in Excel to perform various operations easily and swiftly. Sometimes, we need to use hexadecimal numbers and binary numbers for various purposes. In addition, we need to convert them and use them in various operations. For the session, I am using Microsoft Office 365. However, you can use your preferred version. In this article, I will show you 2 suitable methods to convert Hex to Binary in Excel. Now, without further ado, let’s begin.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

What Are Hex Numbers?

Hex numbers, i.e., Hexadecimal numbers, are the numbers based on 16. Normally, we use decimal numbers in daily life that are based on 10 digits, i.e. 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. But hex numbers have six more digits. They are 10, 11, 12, 13, 14, and 15. Now, to write a number, you need to place a digit in a single place. But some of the digits of hexadecimal numbers need two places to be written if you think from a decimal perspective. So, in the hexadecimal number system, we replace the two-place digits (10, 11, 12, 13, 14, and 15) with some English alphabets.

For example, 10 is replaced with A, and B refers to 11, while C, D, E, and F represent 12, 13, 14, and 15, respectively.

What Are Binary Numbers?

In general, there are four types of number systems, and the binary number is one of them. Usually, Binary numbers contain only 2 digits, 0 and 1. Other digits are not applicable here. However, all binary numbers are formed using these 2 digits. Moreover, it is known as the base-2 numeral system.

2 Suitable Methods to Convert Hex to Binary in Excel

In this article, I will discuss 2 quick and suitable methods to convert Hex to Binary in Excel. Moreover, this section provides step-by-step procedures and details for all methods, and I have used Microsoft Office 365. However, it includes the HEX2BIN function and VBA code. For the purpose of demonstration, I have used the following sample dataset in Excel.

1. Utilize HEX2BIN Function to Convert Hex to Binary in Excel

Fortunately, you can convert Hex to BInary using functions directly. In this part, I will utilize the HEX2BIN function in order to complete the operation easily and swiftly. However, the function can not process numbers with more than 10 characters or bits, and it will show a value error if the number is text or non-numeric. Hence, follow the steps below.

Steps:

• Initially, select cell C5 and insert the following formula.

• Finally, utilize the AutoFill tool for the entire column to get the final output.

2. Transform Hex Numbers to Binary Through VBA Code

Furthermore, a VBA code can also help you to convert Hex to Binary in Excel. A VBA code is necessary for this method. However, you can just copy the code and run it in your Excel worksheet. Hence, go through the following the below steps.

Steps:

• Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
• Secondly, click the Insert button and select Module from the menu to create a module.

• Thirdly, a new window will open and write the following VBA macro in the Module.

Public Function NEW_HEX2BIN(strHEX As String) As String
    Dim bits As Long, preface As Long, p As Long
    Dim zero As String * 4
    For bits = 1 To Len(strHEX)
        preface = 0
        zero = "0000"
        p = Val("&H" & Mid$(strHEX, bits, 1))
        While p > 0
            Mid$(zero, 4 - preface, 1) = p Mod 2
            p = p \ 2
            preface = preface + 1
        Wend
        NEW_HEX2BIN = NEW_HEX2BIN & zero & ""
    Next
    NEW_HEX2BIN = RTrim$(NEW_HEX2BIN)
End Function

• Fourthly, press the F5 key to run the VBA code.
• After that, write the following formula on cell C5.

• Finally, press Enter and apply the AutoFill tool to get similar output to the entire column.

How to Convert Hex to Binary for Large Numbers in Excel

Usually, you can convert Hex to Binary for less than 10 bits using functions. But you can easily convert Hex to Binary for more than 10 bits by applying VBA code. However, the process is quite similar to the previous one. For the purpose of demonstration, I have used the following sample dataset.

Steps:

• First of all, follow the steps mentioned in the second method in this article to get the Module dialog box.
• Afterward, insert the following code into the module.

Public Function NEW_HEX2BIN(strHEX As String) As String
    Dim bits As Long, preface As Long, p As Long
    Dim zero As String * 4
    For bits = 1 To Len(strHEX)
        preface = 0
        zero = "0000"
        p = Val("&H" & Mid$(strHEX, bits, 1))
        While p > 0
            Mid$(zero, 4 - preface, 1) = p Mod 2
            p = p \ 2
            preface = preface + 1
        Wend
        NEW_HEX2BIN = NEW_HEX2BIN & zero & ""
    Next
    NEW_HEX2BIN = RTrim$(NEW_HEX2BIN)
End Function

• Similarly, apply the formula and apply the AutoFill tool to get the final output.

Conclusion

These are all the methods you can follow to convert hex to binary in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.

The post How to Convert Hex to Binary in Excel (2 Easy Ways) appeared first on ExcelDemy.