Excel cannot recognize hexadecimal numbers as numeric values. However, there is a function in Excel that can convert hex values into decimal values. To use special characters within Excel cells, ASCII codes are required. These codes give more meaning to the data and make it easier to understand.
What are the Hexadecimal Numbers?
The Hexadecimal numbers, also called base-16 or hex, represent numbers using a combination of the digits 0-9 and the letters A-F.
So its 16 symbols are (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F). Where (A, B, C, D, E, F) use for (10, 11, 12, 13, 14, 15). The hexadecimal system is often used in computer programming because it is a more human-friendly way of representing binary data.
What is the ASCII Code?
The ASCII code is a character encoding system used to represent text in computers and other devices. The ASCII code assigns a numeric value to each letter of the alphabet, which can then be used to describe the text. The ASCII code is a standard used by many computer systems and is also the basis for the Unicode character encoding system.
It is a standard for allocating numbers, letters, and other characters within the 256 spaces of the 8-bit code. It can be found in Hexadecimal from 00 through FF.
How to Converting Hex to ASCII in Excel?
If you need to convert hex to ASCII in Excel, there are a few different ways you can do so. One method is to use the HEX2DEC and CHAR functions.
Another method is to use the Excel VBA Code.
Here we explain both methods with an example.
The first method is to use the HEX2DEC and CHAR functions. This function will convert hexadecimal value to decimal value and then finally convert ASCII value.
Let’s see how it will work with an example-
Here we create a data sheet to show how this method can be done.
- At first, we convert the hex value to a dec value. So, we must select a cell to apply the formula =HEX2DEC(Number of cells). Here we choose cell B2, and our formula is =HEX2DEC(A2).
- Now press the Enter and Fill Handle to drop down your cursor to copy the formula.
- Here we get all the Decimal values. Now we convert them to ASCII values. In the same process, we have to select a cell to apply the formula =CHAR(Number of a cell). Here we choose the C2 cell. And our formula is =CHAR(B2).
- Press the “Enter” key and “Fill Handle” to drop down your cursor to copy the formula.
The CHAR function returns a character specified by the code number in your computer’s character set. In other words, the hexadecimal code “30” has been converted into the ASCII number of “0” and so forth.
This method is for you if you have little knowledge of working with Excel VBA.
We will use a data sheet to apply the following method.
Here we used a user-defined function to convert hex value to
- To open the Visual Basic Editor, press the Alt + F11 keys.
2. Open the Visual Basic Editor window.
3. Here you have to create a new module. So, in the project window, you have to right-click on your worksheet and insert a module. In this new module have to enter the following code-
4. Save your workbook.
5. Press Alt + F11 keys to switch to the active worksheet.
6. Now you must select a cell to apply the formula =Hex2ASCII(Number of a cell). Here we choose cell B3, and our formula is =Hex2ASCII(A3).
7. Use the Enter key to drag the Fill handle down.
Here we convert the hexadecimal value to ASCII. You can use this User Defined Function to convert any hexadecimal number to an ASCII.