How to Convert Hex Numbers to Binary in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

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.

excel hex to binary


1. Utilizing the 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.

=HEX2BIN(B5)

Utilize HEX2BIN Function to Convert Hex to Binary in Excel

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


2. Transforming 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 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

Apply VBA Code to Convert Hex to Binary in Excel

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

=NEW_HEX2BIN(B5)

  • 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

Apply VBA Code to Convert Hex to Binary in Excel

Note:

The code is the same for large and small numbers. So, you need not input the code twice if used earlier.

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

=NEW_HEX2BIN(B5)

How to Convert Hex to Binary for Large Numbers in Excel


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.


<< Go Back to | Excel Number System Conversion | Excel for Math | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo