Convert Hexadecimal Values to ASCII in Excel

Excel does not recognize hexadecimal values as numeric values. It has a function to convert hex values to decimal values but not one to convert them to ASCII values. To use special characters in Excel Cells, we will need the help of ASCII codes. They provide more meaning to the information and also make data easier to understand.

Hexadecimal numbering system

The hexadecimal numbering system utilizes the Base 16 system, meaning it has 16 possible digit symbols.

It uses 16 symbols which are {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F} to represent all numbers. The {A, B, C, D, E, F} stands for {10, 11, 12, 13, 14, 15}.

ASCII

ASCII is the acronym for American Standard Code for Information Interchange. It is a standard that allocates numbers, letters, and other characters within the range of the 256 spaces available in the 8-bit code. It ranges from 00 to FF in Hexadecimal. For example, the Hexadecimal value of “30” is the value “0” in ASCII.

Given a hexadecimal value, many people resort to going to the web to look up ASCII character code charts.

It would be more convenient if this could be done automatically in Excel.

2 Methods of converting hexadecimal values to ASCII in Excel

In this tutorial we will look at the following 2 methods of converting Hex values to ASCII:

  1. Use HEX2DEC and CHAR functions
  2. Use Excel VBA Code

Method 1- Use HEX2DEC and CHAR functions

This method works by first converting the hexadecimal value to a decimal value and then finally converting the decimal value to an ASCII value.

We will use the following dataset to show how this can be done:

Step 1 – Select Cell B3 and key in the formula =HEX2DEC(A3) as follows:

Step 2 – Press the Enter key and use the Fill Handle to copy the formula down:

Step 3 – Select Cell C3 and enter the formula =CHAR(B3) as follows:

Step 4 – Press Enter key and drag down the Fill Handle to copy the formula to other cells as follows:

The CHAR function returns the character specified by the code number from the character set of your computer. It can be seen that the hexadecimal value “30” has been converted to the ASCII value of “0”, and so on.

Method 2 – Use Excel VBA Code

This method will require that one has a working knowledge of Excel VBA.

We will use the following dataset to demonstrate how this method works:

We will use a User Defined Function to convert hexadecimal values into ASCII values by doing the following steps:

Step 1 – Press Alt + F11 to open the Visual Basic Editor:

Step 2 – In the Project Window right click your worksheet and insert a new module.

Step 3 – In the new module, enter the following code:

Public Function HEX2ASCII(ByVal hextext As String) As String
    Dim y As Integer
    Dim num As Integer
    Dim Value As Integer
    For y = 1 To Len(hextext)
        num = Mid(hextext, y, 2)
        Value = Value & Chr(Val("&h" & num))
        y = y + 1
    Next y
    HEX2ASCII = Value
End Function

Step 4 – Save the workbook and switch back to the active worksheet by pressing Alt + F11

Step 5 – Select Cell B3 in the dataset and enter the formula =Hex2ASCII(A3) as follows:

Step 6 – Press the Enter key and drag down the Fill Handle:

The hexadecimal values are converted to ASCII values. You can use this User Defined Function to convert any hexadecimal value to an ASCII value.