Al Ikram Amit
In this article, we will see how to format hex numbers with leading zero in Excel. In many cases, we require numbers that start with single or multiple zeros e.g., phone number or zip code. As our topic deals with adding a leading zero to hexadecimal numbers, the methods or suggestions in the following sections will give you a vibrant outlook on how to add a leading zero to a number. Our main focus point however will be adding leading zero to hexadecimal numbers in Excel with proper format.
Download Practice Workbook
You can download the practice workbook from the following download button.
What Are Hex Numbers?
Hex numbers, i.e. hexadecimal numbers are the numbers based on 16. We normally use decimal numbers in daily life which are based on 10 digits, i.e. 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. But hex numbers have 6 more digits. They are 10, 11, 12, 13, 14, and 15.
Now, to write a number you need to place a digit in a single place. But some of the digits of hex numbers need 2 places to write them if you think from a decimal perspective. So, in the hex number system, we replace the two-place digits (10, 11, 12, 13, 14 & 15) with some English alphabets.
For example, 10 is replaced with A, and B refers to 11, C, D, E, and F represent 12, 13, 14, and 15 respectively.
Steps to Format Hex Numbers with a Leading Zero in Excel
Now, we will see how to format these hex numbers with leading zero using suitable Excel tools.
⦿ Step 1: Convert Decimal Numbers to Hex Format Using DEC2HEX Function in Excel
- Suppose we have decimal numbers in column B and Excel has a built-in function to convert decimal to hexadecimal.
- Now, by entering the following formula we can convert the decimal numbers into hexadecimal:
=DEC2HEX(B5)
- Now, dragging the fill handle down, we can convert all decimal numbers to hexadecimal numbers.
Read More: How to Convert Hex to Decimal in Excel (2 Practical Examples)
⦿ Step 2: Format Hex Numbers with Leading Zero Using Excel RIGHT Function
- Using the RIGHT function is the most convenient way to add a leading zero to a hexadecimal number. The generic formula with its argument is given below:
RIGHT("Zeros" & cell, string length)- In cell D5, we will enter the following formula to add zero before the hexadecimal number:
=RIGHT("0"&C5, 6)
- Now again we will drag the fill handle down to complete the series where we can see in the following image that hexadecimal numbers like A, B, C, D, E, and F all start with a leading zero.
- Note that adding a leading zero with the Text function will not be fully possible for hexadecimal numbers like A, B, C, D, E, and F.
Read More: How to Convert Hex Numbers to Binary in Excel (2 Easy Ways)
Things to Remember
- We can use this method in other words utilize the TEXT function where all numbers are in a numeric value like 1,2,3… but not an alphabet.
- Important thing to note is that we are showing this method because this one has a limitation in the hexadecimal number system but not in the binary, octal, or decimal number system.
- First of all, we will enter the following formula to do the job of adding a lead zero to a hexadecimal number:
=TEXT(C14,"00")
- Now we will drag the fill handle down to copy the formula for the rest of the cells.
- Note that, the TEXT function has failed to add a leading zero before A, B, C, D, E, F.
Conclusion
This way, we can keep any number of leading zeros in the Excel date format if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.
