ExcelInsider Team
Hexadecimal (Hex) is a base-16 number system using 16 digits: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F. In hexadecimal numbers A = 10, B = 11, …, F = 15 in decimal.Decimal is a base-10 number system, using digits 0-9. When working with large datasets, interpreting hex values can be challenging. That’s why you need to convert hexadecimal values to decimal for easier analysis, calculations, or reporting. Converting hexadecimal numbers to decimal in Excel is quite a simple task. You can convert it easily by applying some direct functions & formulas.
To convert hex to decimal in Excel with HEX2DEC function, follow the steps below:
➤ Write formula in the cell E2 : =HEX2DEC(D2)
➤ Press Enter to see the decimal value
➤ Drag the formula down to convert the hex values to decimal values.
This article outlines four different methods for converting hexadecimal numbers to decimal in Excel. Using Excel’s HEX2DEC() Function, Using DECIMAL() Function, and Using a Custom VBA Function are the three ways to convert hexadecimal numbers to decimal numbers in Excel.
Using Excel’s HEX2DEC Function
In Microsoft Excel, there is a built-in HEX2DEC function that converts directly from hexadecimal number into decimal.
Look at the dataset below, where column D has hexadecimal numbers. We will convert these hexadecimal numbers to decimal numbers in column E.
Steps:
➤ Click on the cell E2 and write this formula :
=HEX2DEC(D2)
➤ Press Enter and see the decimal value of A5, which is 165.
➤ Drag the formula down to see other decimal values
Inserting DECIMAL Function
When you are using the DECIMAL function to convert a hexadecimal value, Excel evaluates each digit from left to right and then assigns place values based on the base. That means hexadecimal “A” will be 10. After that, starting from right to left, Excel places the power of its position. Power placing starts with 0 from right to left. Multiply each digit by 16. After that sums the result.
For example, the formula =DECIMAL(“1A”, 16) returns 26, because “1A” represents (1 × 16¹) + (10 × 16⁰) = 16 + 10 = 26.
Steps:
➤ Click on the cell E2 and write this formula :
=DECIMAL(D2, 16)
➤ Press Enter and see the decimal value of 9A.
➤ Drag the formula down to see other decimal values.
Using a Custom VBA Function
When you are using earlier versions of Excel, there is no built-in function. So you need to use a custom VBA function to convert hex values to decimal.
Steps:
➤ Press Alt + F11 to open the VBA editor.
➤ Insert a new module: Insert > Module
➤ Paste this code and Press Ctrl + S to save the workbook as .xlsm format and then Close the VBA editor.
Function HexToDecimal(hexVal As String) As Long
HexToDecimal = Val("&H" & hexVal)
End Function
➤ Go back to Excel file and use your new custom formula in the cell E2:
=HexToDecimal(D2)
Press Enter and see the decimal value of “AA”, which is 170
➤ Drag the formula down to see other decimal values.
Note:
You need to save your file as .xlsm format. (File > Save As > Select Format: .xlsm). Otherwise, the macro won’t be saved. You can see a yellow bar at the top. Click “Enable Content” so your macro can run.
Frequently Asked Questions
Will the decimal value be the same if the hex value is “a3” or ff instead of “A3” OR “FF”?
The decimal value will be the same for the hex value “a3” or “ff” instead of “A3” or ” FF“. because both HEX2DEC() and DECIMAL() functions in Excel are not case sensitive. So if your hex value is “a3” or “ A3”, the decimal value will be 163. If your hex value is “ff” or “ FF”, the decimal value will be 255.
What’s the maximum hex value I can convert into decimal value?
The maximum hex value is “7FFFFFFF“, whose decimal value is 2,147,483,647.
Why the #NUM! Error is displaying?
You can see the #NUM! error in the cell if the hex value is beyond A–F, which is considered an invalid hex value, and if the value is too large.
Wrapping Up
In this article, we have discussed different ways to convert hexadecimal numbers to decimal numbers. Conversion processes are shown step by step from hex to decimal using Excel’s HEX2DEC() Function, using DECIMAL() Function, and using a Custom VBA Function. Feel free to download the practice file and share your thoughts and suggestions in the comments section.
