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

Get FREE Advanced Excel Exercises with Solutions!

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:
=DEC2HEX(B5)

Convert Decimal Numbers to Hex Format Using DEC2HEX Function in Excel

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

dragging the fill handle down

Read More:  How to Convert Decimal to Binary in Excel


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

Format Hex Numbers with Leading Zero Using Excel RIGHT Function

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

again we will drag the fill handle down to add a leading zero

Read More: How to Convert Binary to Decimal in Excel


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")

Utilize the TEXT function

  • Now we will drag the fill handle down to copy the formula for the rest of the cells.

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.

the Text Function has failed to add a leading zero before A, B, C, D, E, F

Read More: How to Convert Hex to Decimal in Excel


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Hello, I'm Amit, a BUET graduate with a passion for Excel. Currently, I work as an Excel & VBA Content Developer at ExcelDemy, contributing insightful articles to the blog. I enjoy sharing my knowledge with others and always look for opportunities to grow as an Excel expert. Feel free to reach out if you need assistance or want to discuss the latest trends in Excel. Let's excel together in the world of data manipulation and analysis!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo