The HEX2DEC Function in Google Sheets is categorized under the Engineering function. Its purpose is to covert a maximum of 10 characters long (40-bit) hexadecimal number to decimal.

10 Characters long and 40-bit hexadecimal number! What’s that?

Let me explain it. Hexadecimal is a base 16 system in computing. The 16 hex digits are 0 1 2 3 4 5 6 7 8 9 A B C D E F. Each hex digit has a 4-bit binary equivalent.

That means with the HEX2DEC function in Google Sheets you can convert a maximum 40-bit hexadecimal number to decimal.

In the hex digits, “0” to “9” represents the values 0 to 9 and “A” to “F” or “a” to “f” represents the values 10 to 15.

If you use 11 characters (44-bit) or more in the formula, it will return NUM! error. Hover your mouse on the tooltip to see the cause of the error. It will be like “Function HEX2DEC parameter 1 is too long”.

Google Sheets HEX2DEC Function Syntax and Argument

Syntax:

HEX2DEC(signed_hexadecimal_number)

Argument:

signed_hexadecimal_number – The signed hexadecimal number to convert to decimal. It has the following characteristics.

• The number cannot contain more than 10 characters (40 bits).
• The most significant bit of the signed_hexadecimal_number is the sign bit. The remaining 39 bits indicate the magnitude of the signed_hexadecimal_number.
• Negative numbers are represented using two’s complement (refer wiki) format/notation.

Examples to HEX2DEC Function in Google Sheets

Example 1:

Example 2:

The above HEX2DEC formulas return the equivalent decimal values of 16 hex digits. Now in the following example, I am using alphanumeric characters as the signed_hexadecimal_number in the HEX2DEC function.

The formula =HEX2DEC("5F") in Google Sheets will return the decimal number 95. Do you know the calculation in that behind?

It’s like 5*16+15. That means =HEX2DEC("5")*16+HEX2DEC("F"). Hope you could learn it.

Example 3:

The below HEX2DEC formula in Sheets is equal to =(2*16*16)+(2*16)+2, which would return 546.

=HEX2DEC("222")

Example 4:

See one more example the HEX2DEC function in Google Sheets. It is also in line with the above example formula.

=HEX2DEC("000901")

This HEX2DEC formula returns the decimal number 2305 and the equivalent is =9*16*16+1. You can actually read it as =9*16^2+1.

Example 5:

As I have mentioned in the argument part above, negative numbers are represented using two’s complement notation.

=HEX2DEC("FFFFFFFFFB")

Result: -5

Here is the calculation behind the above HEX2DEC formula in Sheets.

=-16^9+15*16^8+15*16^7+15*16^6+15*16^5+15*16^4+15*16^3+15*16^2+15*16+11

You can refer to the following official Google Sheets documentation for more details about the use of the HEX2DEC function – Docs Editors Help.

Converting Unicode Value in Hexadecimal to Equivalent Character

By using the function CHAR in Sheets, we can convert decimal numbers into Unicode table characters.

For example, to insert a heart symbol in Sheets, you can use the below CHAR formula.

=char(10084)

Related Reading: Inserting Bullet Points in Google Sheets.

But, as you can see, many of the Unicode tables provide Unicode values in hexadecimal format. For example, the hexadecimal number of the filled heart symbol above is 2764.

Assume you only have this hexadecimal number, not the decimal used in the CHAR formula above.

In this case, you can use the HEX2DEC function to convert the hexadecimal (base 16) number to decimal and use that number in the CHAR formula.

=char(HEX2DEC(2764))

That’s everything about how to use the HEX2DEC function in Google Sheets. Enjoy!