Tanjim Reza
When working with numbering systems, Hexadecimal and Decimal both systems are significant from their own perspectives. So, it is a frequent need for us to convert hex to decimal or convert decimal to hex. In this article, I will show you how to convert hex to decimal in Excel with 2 practical examples.
Download Practice Workbook
You can download our practice workbook from here for free!
2 Practical Examples to Convert Hex to Decimal in Excel
1. Using HEX2DEC Function to Convert Hex to Decimal
Say, you have a dataset containing valid hexadecimal numbers.
Now, you can convert these hex numbers into decimal numbers by using the HEX2DEC function. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on cell C5.
- Following insert the formula below and press the Enter key.
=HEX2DEC(B5)
- Consequently, you will get the decimal number for the first hexadecimal number.
- Now, place your cursor in the bottom right position of cell C5.
- Subsequently, a black fill handle will appear.
- Following, drag it down to copy the same formula for all the other cells below.
Thus, you will get all the hexadecimal numbers converted into decimal numbers successfully. Besides, it would work as a hex2dec converter. You can give any hexadecimal values in cell B5 to B12 and you would get decimal values in cell C5 to C12.
And, the final result should look like this.
Read More: How to Convert Binary to Decimal in Excel (2 Easy Methods)
2. Convert Hexadecimal Numbers with Unwanted Digits to Decimal Numbers
Now, say you have a dataset of hexadecimal numbers starting with 0x. But, these two first digits are unnecessary, and besides x is out of digits for hexadecimal numbers.
Now, you need to remove these and get actual hexadecimal numbers converted into decimal numbers. You can accomplish this by using any of the ways that are given below.
i. Using the RIGHT Function
You can use the RIGHT and LEN functions with the HEX2DEC function to achieve your target here. Follow the steps below to do this.
📌 Steps:
- At the very beginning, click on cell C5 and insert the following formula.
=HEX2DEC(RIGHT(B5,LEN(B5)-2))
🔎 Formula Breakdown:
- =LEN(B5)-2
It would calculate the length of cell B5 and subtract 2 from the result.
Result: 7
- =RIGHT(B5,LEN(B5)-2)
It would show the B5 cell’s particular length from the right side. The particular length will be determined by the previous step.
Result: 0018276
- =HEX2DEC(RIGHT(B5,LEN(B5)-2))
This would convert the previous resultant hexadecimal to decimal.
Result: 98934
- Subsequently, hit the Enter key.
- Afterward, place your cursor in the bottom right position of the cell and drag the fill handle below upon its arrival.
Thus, you will get the same formula for all the other cells and the final result would look like this.
ii. Using the SUBSTITUTE Function
You can also incorporate the SUBSTITUTE function with the HEX2DEC function to achieve your desired result. Follow the steps below to do this.
📌 Steps:
- First, click on cell C5.
- Following, insert the formula below and hit the Enter key.
=HEX2DEC(SUBSTITUTE(B5,"0x",""))
🔎 Formula Breakdown:
- =SUBSTITUTE(B5,”0x”,””)
It would substitute the 0x digits from cell B5 with a blank digit.
Result: 0018276
- =HEX2DEC(SUBSTITUTE(B5,”0x”,””))
It would convert the previously resultant hexadecimal into decimal.
Result: 98934
- As a result, you will get the decimal value perfectly for cell B5.
- Following, use the fill handle feature to get all the other values
As a result, you will get all the decimal values converted from the hexadecimal values. For instance, the final result would look like this.
ii. Using the REPLACE Function
Moreover, you can use the REPLACE function to convert hexadecimal to decimal numbers. Follow the steps below to accomplish this target.
📌 Steps:
- Initially, click on cell C5 and insert the following formula.
=HEX2DEC(REPLACE(B5,1,2,""))🔎 Formula Breakdown:
- =REPLACE(B5,1,2,””)
It would replace the first 2 digits from cell B5 with a blank digit.
Result: 0018276
- =HEX2DEC(REPLACE(B5,1,2,””))
It would convert the previously resultant hexadecimal to decimal.
Result: 98934
- Subsequently, hit the Enter key.
- Consequently, you will get the decimal result for cell B5.
- Now, use your fill handle feature to copy the same formula for the other cells.
Thus, you will get all the values for the hexadecimal numbers and the result should look like this.
Read More: Format Hex Numbers in Excel with Leading Zero (with Quick Steps)
💬 Things to Remember
- Hexadecimal numbers contain 10 digits at most. Numbers with more than 10 digits will result in #NUM! error.
- Hexadecimal numbers can contain 1-9 numerical digits and A-F alphabetical digits. Any digit other than these digits will return a #NUM! error.
Conclusion
So, in this article, I have shown you 2 practical examples to convert hex to decimal in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!
