Format Hex Numbers in Excel with Leading Zero (with Quick Steps)

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.

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:

• Now, dragging the fill handle down, we can convert all decimal numbers to hexadecimal numbers.

⦿ 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:

• In cell D5, we will enter the following formula to add zero before the hexadecimal number:

• 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.

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:

• 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.

Download Practice Workbook

You can download the practice workbook from the following download button.

Conclusion

This way, we can keep any number of leading zeros in the Excel date format if we study this article properly. If you have any questions, comments or queries, please let us know in the comment box below.

<< Go Back to | Excel Number System Conversion | Excel for Math | Learn Excel